Multiple Key Clustering In Db2 8.1 - Interesting FYI

Dear hackers,


I have recently been playing with DB2 8.1 Beta. It has introduced a 
feature to enable index clustering on more than one key. This reminded 
me of a previous thread on HACKERS about index access anding/bitmaps in 
Firebird. So anyway, here is a little snip from the 8.1 manual as a FYI. 

-- snip

As the name implies, MDC tables cluster data on more than one dimension. 
Each dimension is determined by a column or set of columns that you 
specify in the ORGANIZE BY DIMENSIONS clause of the CREATE TABLE 
statement. When you create an MDC table, the following two kinds of 
indexes are created automatically:
   * A dimension-block index, which contains pointers to each occupied     block for a single dimension.   * A
compositeblock index, which contains all dimension key columns.     The composite block index is used to maintain
clusteringduring     insert and update activity.
 

The optimizer considers dimension-block index scan plans when it 
determines the most efficient access plan for a particular query. When 
queries have predicates on dimension values, the optimizer can use the 
dimension block index to identify, and fetch from, only extents that 
contain these values. In addition, because extents are physically 
contiguous pages on disk, this results in more efficient performance and 
minimizes I/O.

--  snipped


regards

Mark