Databases Reference
In-Depth Information
Figure 8.9
Data roll-out with MDC.
MY_TABLE example. These serve to illustrate the runtime exploitation of MDC with
various kind of SQL queries, predicates, and operators.
1. SELECT year(DATEOFSALE), avg(SALES), COLOR FROM MY_TABLE
WHERE REGION = 'ON'
GROUP BY (COLOR, YEAR(DATEOFSALE));
In this case we are grouping by COLOR and the year of the DATEOFSALE, and filter-
ing by REGION. The GROUP BY feature will require the RDBMS to evaluate the
select result per unique instance of COLOR value and year. Clustering by all of (or any
of ) COLOR, REGION, or DATE means that the data in each row of the result set can
be fetched by the RDBMS without filtering on COLOR or REGION and the mini-
mum I/O will be performed for each row.
2. SELECT * FROM MY_TABLE
WHERE DATEOFSALE < '12/30/1999';
This involves a range predicate on a single dimension, so it can be internally rewritten
to access the table using the dimension index on DATEOFSALE. The index will be
scanned for the locations of all cells/blocks with values less than '12/30/1999', and a
minirelational scan will be applied to the resulting set of blocks to retrieve the actual
records.
3. SELECT * FROM MY_TABLE
WHERE REGION IN ('NY', 'CA');
