New Challenges in Physical Database Design - Automated Physical Database Design and Tuning - page 218

1
2
3
4

Richer work loads
Other recommendations
Interaction mode
Benchmarks

DBMS

4

3

2

1

Tuning Tool Recommendation

Workload

FIGURE 12.1 Some open-ended challenges in physical database

design.

SELECT SUM(price)
FROM sales
WHERE country = 'USA'
AND 03/01/2010 <= date < 04/01/2010

If the input workload was gathered over a couple of months, there might
be multiple variations of this query, each one with a different country and
date ranges. Techniques such as index merging can take advantage of such
workloads by combining multiple range predicates into larger ones (e.g., if the
workload spans 3 months' worth of reports, a recommended materialized view
can contain a predicate on date that covers the full period). This example,
however, introduces the following challenges:

Constants vs. parameters: Not all constants in a query should be treated
in the same way. For instance, if the company always requires a report
of all the sales in the U.S., the constant in predicate country = 'USA'
can be included in a predicate of a materialized view. Other constants
are just instances of parameter for templatized queries. For instance,
in the previous query, it is likely that date ranges always vary across
reports. Therefore, including any specific date range (or even a merged
range) as a predicate in a materialized view might not be beneficial in
general, since future queries are likely to be defined on different ranges.
Temporal workloads: Consider again the previous reporting query, and
suppose that it is part of a workload gathered over a month. It is very
likely that this and other similar queries refer to the same month of
March (probably because it is the current—or past—month). In that
case, it is highly likely that in the following month, the workload shifts
date ranges to the month of April. Although implicit, there is a hint
that queries referring to current periods of time are evolving temporal
queries. These situations might not always be simple to detect auto-
matically and can have a big influence in both static and continuous
physical design tuning.