Geek Answers Handbook

Unique indexes and include statements

create a unique index

In DB2 UDB, I can create an index using the following syntax

create unique index I_0004 on TABLENAME (a) INCLUDE (b, c, d);

where a, b, c and d are the fields in the table TABLENAME .

In DB2 for os390, this syntax ( INCLUDE keyword) is not valid, so I create indexes as follows

 create unique index I_0004 on TABLENAME (a); create index I_0005 on TABLENAME (a, b, c, d);

Are the two statements above equivalent to a solution with the INCLUDE keyword?

index column order

And, if I slightly changed the first statement

 create index I_0005 on TABLENAME (a, b, c, d) ALLOW REVERSE SCANS;

is the ALLOW REVERSE SCANS equivalent of creating indexes

 create index I_0005 on TABLENAME (a, b, c, d); create index I_0005 on TABLENAME (d, c, b, a);

or does he also consider any combination of these columns (I mean, a, b, c, d; b, c, d, a; c, d, a, b, etc.)?

+1
source share
1 answer

Regarding UNIQUE INDEX : roughly, yes, a unique index on (a) , including (b, c, d) , is equivalent to a unique index only on (a) plus not unique on (a, b, c, d) - except, of course that the internal database engine can use less space and c.

Regarding ALLOW REVERSE SCANS : no, the index on (a, b) that can be scanned from the back is not equivalent to the index that cannot plus one on (b, a) - rather, the index that can be scanned back is equivalent to one that cannot plus the other in the same columns where each ASC becomes DESC and vice versa (and ASC is the default when you do not specify).

Please note that since DB2 9.1 reverse scanning is enabled by default, see http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.rn. doc / doc / c0023548.htm (and I believe that DB2 V8 is no longer support, see http://www-01.ibm.com/support/docview.wss?rs=71&uid=swg21370360 - I think that V9.5 is the current version).

+1
source

More articles:


All Articles