Databases Reference
In-Depth Information
This involves an IN predicate on a single dimension, and can trigger dimension index-
based scans. This query can be internally rewritten to access the table using the dimen-
sion index on REGION. The index will be scanned for the locations of keys with values
of 'NY' and 'CA', and a minirelational scan will be applied to the resulting set of blocks
to retrieve the actual records.
4. SELECT SUM(SALES) FROM MY_TABLE
WHERE DATEOFSALE > '01/01/1998'
AND COLOR = 'BLUE'
AND REGION IN ('NY', 'CA');
This involves a range predicate on DATEOFSALE, an equality predicate on COLOR,
an IN predicate on REGION, along with an AND operation. This can be internally
rewritten to access the table on each of the dimension block indexes. A scan of the
DATEOFSALE dimension index will be done to find the locations of keys with values
greater than '01/01/1998', of the COLOR dimension index to find those with value
BLUE, and of the REGION dimension index to find those whose values are either 'NY'
or 'CA'. Index ANDing will then be done on the resulting locations from each block
scan to find their intersection, and a minirelational scan will be applied to the resulting
set of blocks to find the actual records.
5. SELECT ...... FROM MY_TABLE
WHERE DATEOFSALE <'01/01/1997' OR REGION IN ('NV', 'WA' );
This involves a range predicate on the DATEOFSALE dimension and an IN predicate
on the REGION dimension, as well as an OR operation. This can be internally rewrit-
ten to access the table on the dimension block indexes DATEOFSALE and REGION.
A scan of the DATEOFSALE dimension index will be done to find values less than '01/
01/1997' and another scan of the REGION dimension index will be done to find val-
ues 'NV' and 'WA'. Index ORing will be done on the resulting locations from each
index scan, then a minirelational scan will be applied to the resulting set of blocks to
find the actual records.
6. SELECT ...... FROM MY_TABLE,d1,d2,d3
WHERE MY_TABLE.REGION = d1.c1 and d1.region = 'CA'
AND MY_TABLE.DATEOFSALE = d2.c3 and d2.year='1994'
AND MY_TABLE.PRODUCT_CLASS = d3.c3 and
d3.product_COLOR = 'BLUE';
This involves a star join. In this example, MY_TABLE is the fact table and it has foreign
keys REGION, DATEOFSALE, and PRODUCT_CLASS corresponding to the pri-
mary keys of d1, d2, and d3, the dimension tables. The dimension tables do not have to
