Databases Reference
In-Depth Information
purpose of the suggestions is to provide tangible examples of co-located
ITEMSET_KEY values. Even if the suggestions have become obsolete, the
intention is to communicate the optimization of the recursive join by co-
locating all Market Basket Table rows by their ITEMSET_KEYs.
Teradata
All rows are assigned a d ata block based on the hash value of the
Primary Index of a table. For the Market Basket Table, define the column
ITEMSET_KEY as the Primary Index. Doing so will cause all rows with
the same ITEMSET_KEY value to be located in one hash location. The
recursive join will explain as a ROW HASH MATCH SCAN, which will
mean that the RDBMS knows the rows are all in the same hash location
and do not need to move during the join process. As a result, the recursive
join will not redistribute any rows. Rather, it will leave them in place.
Oracle
Oracle uses a Clustered Index to assign rows to data blocks. That assign-
ment is based on the value of the Cluster Key. By defining the ITEMSET_
KEY as the Cluster Key, all the rows in a Market Basket Table with the
same ITEMSET_KEY value will have the same Cluster Key and will reside
in the same data block because the Clustered Index places them there.
A Clustered Index can be used to locate rows from two tables in the
same data block. Even though the Market Basket application has only one
Market Basket Table, the recursive join treats the one Market Basket Table
as two tables that coincidentally happen to be completely identical to each
other. The assignment of the join column as the Cluster Key optimizes the
join between those two table aliases because the rows that will join to each
other are in the same cluster, and the RDBMS knows they are in the same
cluster.
DB2
The DB2 RDBMS includes two distinctly different platforms. DB2 for
OS/390 operates on a ma inframe. DB2 UDB operates on Linux, Unix,
and Windows. Both versions of DB2 include a c lustered index feature,
which physically locates all rows in the same page space by the clustered
index key. By defining the ITEMSET_KEY as the clustered index key,
