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 composite block index, which contains all dimension key columns.
The composite block index is used to maintain clustering during
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