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, graphics/ccc.gif(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