MDC Indexes
When an MDC table is created, DB2 will automatically create the block dimension and regular dimension indexes, if needed.
NOTE
In the initial release of DB2 Version 8, the index is being created in the reverse order from what is described in the documentation, when there are composite dimensions. It is important to note that this will change in FixPack 1.
The following examples will show the current index definition, as well as how the index will be defined once FixPack 1 is installed. It is also important to note that existing block indexes will not be dropped and recreated when FixPack 1 is installed.
create table city(name char(30), zipcode char(10), state char(2)) organize by (state)
The relevant information from the SYSCAT.INDEXES view is below:
COLNAMES INDEXTYPE ------------- ----------- +STATE DIM
In this case, because there is only one dimension column, there will be a dimension index on the column STATE, and no block dimension index because block dimension indexes are required only when there is more than one dimension. This will not change in FixPack 1.
For the following table:
create table city(name char(30), zipcode char(10), state char(2)) organize by (state,name)
the relevant information from the SYSCAT.INDEXES view is below:
COLNAMES INDEXTYPE ------------- ----------- +NAME+STATE BLOK +STATE DIM +NAME DIM
In this case, there will be three indexes created, a block dimension index on NAME and STATE, a dimension index on STATE, and a dimension index on NAME. The block dimension index will change to STATE and NAME in FixPack 1. After the FixPack, the relevant information from the SYSCAT.INDEXES view is below:
COLNAMES INDEXTYPE ------------- ----------- +STATE+NAME BLOK +STATE DIM +NAME DIM
For the following table:
[View full width]create table city(name char(30), zipcode char(10), state char(2)) organize by (state,(state,name))
the relevant information from the SYSCAT.INDEXES view is below:
COLNAMES INDEXTYPE ------------- ----------- +STATE+NAME BLOK +STATE DIM +NAME DIM
In this case, there will be three indexes created, a block dimension index on STATE and NAME, a dimension index on STATE, and a dimension index on NAME. This will not change in FixPack 1.
For the following table:
create table mdcex1 (c1 int,c2 int,c3 int,c4 int) organize by dimensions (c1,c4,(c3,c1),c2)
the relevant information from the SYSCAT.INDEXES view is below:
COLNAMES INDEXTYPE ------------- ----------- +C2+C3+C1+C4 BLOK +C2 DIM +C3+C1 DIM +C4 DIM +C1 DIM
With FixPack1, the block dimension index information will change to:
COLNAMES INDEXTYPE ------------- ----------- +C4+C3+C1+C2 BLOK
For the following table:
create table mdcex2 (c1 int,c2 int,c3 int,c4 int) organize by dimensions (c1,c2,(c3,c1),c4)
the relevant information from the SYSCAT.INDEXES view is below:
COLNAMES INDEXTYPE ------------- ----------- +C4+C3+C1+C2 BLOK +C4 DIM +C3+C1 DIM +C2 DIM +C1 DIM
With FixPack1, the block dimension index information will change to:
COLNAMES INDEXTYPE ------------- ----------- +C2+C3+C1+C4 BLOK
For the following table:
create table mdcex3 (c1 int,c2 int,c3 int,c4 int) organize by dimensions (c1,(c2,c3),(c2,c1),c4)
the relevant information from the SYSCAT.INDEXES view is below:
COLNAMES INDEXTYPE ------------- ----------- +C4+C2+C1+C3 BLOK +C4 DIM +C2+C1 DIM +C2+C3 DIM +C1 DIM
With FixPack1, the block dimension index information will change to:
COLNAMES INDEXTYPE ------------- ----------- +C2+C3+C1+C4 BLOK
