Databases Reference
In-Depth Information
2.
RID index lookup to find qualifying rows on PartNo.
3.
Block ID and RID ANDing (Figure 8.5).
4.
The result is only those RIDs belonging to qualifying blocks.
8.1.3 Syntax for Creating MDC Tables
There is not a formal standard for the DDL defining MDC tables. The following exam-
ples highlight the syntax for defining MDC in the DB2 implementation. The main
thing to notice is the ORGANIZE BY clause that defines the clustering dimensions.
CREATE TABLE mdctable (
Year INT,
Nation CHAR(25),
Color VARCHAR(10),
... )
ORGANIZE BY( Year, Nation, Color )
Following is syntax for creating an MDC table with coarsification on the Dateof-
sale column, converting it to a year.
CREATE TABLE mdctable (
Dateofsale DATE,
Year generate always as
INT(Dateofsale)/10000,
Nation CHAR(25),
Color VARCHAR(10),
... )
ORGANIZE BY( Year, Nation, Color )
8.2
Performance Benefits of MDC
MDC is predominantly about improving performance by reducing I/O. However, it
can offer some moderate improvements in CPU time because of the increased indexing
efficiency, which reduces the number of index items that needs to be processed by the
RDBMS in order to resolve a query. The following list summarizes the major perfor-
mance advantages of MDC, based on a summary from Kennedy [2005]:
•
Dramatic reduction in I/O requirements due to clustering.
•
The ability to cluster along multiple dimensions independently.
•
Clustering that is guaranteed, not just approximately enforced.
