[HACKERS] Multiple Key Clustering In Db2 8.1 - Interesting FYI

[Mark Kirkwood]

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



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html