Hi,

We use db2 8.2.6 on windows 2000.

I have an elementary question about index/primary key


We have a table that contains only 3 cols - parent(bigint),child(bigint),createdate.

parent-child value is unique (qualify as key)

SQL access always quote either parent or createdate for access.

I created the table with primary key as parent+child and an index on createdate.

Since table has only 3 colmns, does index realy speed performance? Or ther is no need for the primary key or the index

Thanks in advance

Whether or not an index improves performance depends on:

1. The number of rows in the table. If the table has less than a few thousand rows, no index may be needed other than to guarantee uniquneness (unique index or index that is created by defining the primary key).

2. Whether or not DB2 actually uses the index. This may depend on whether DB2 has accurate statistics on the data (from runstats command).

Assuming this is a large table (more than 100,000 rows), and all predicates that query the table have either parent or createdate (as you stated), then I would recommend the following indexes. You must create these objects in the order specified. Note the use of the INCLUDE in the first index (read the SQL Reference Vol 2 for create index for an explanation of INCLUDE).

I have provided 2 options for index TABLE_NAME_IX1. The second option enables "index only" access (the table does not need to be accessed on a select), but the index size is larger.

CREATE TABLE TABLE_NAME ( PARENT BIGINT NOT NULL , CHILD BIGINT NOT NULL , CREATEDATE TIMESTAMP NOT NULL WITH DEFAULT ) ;

CREATE UNIQUE INDEX PK_TABLE_NAME ON TABLE_NAME (PARENT, CHILD) INCLUDE (CREATEDATE );

ALTER TABLE TABLE_NAME ADD CONSTRAINT PK_TABLE_NAME PRIMARY KEY(PARENT, CHILD);

CREATE INDEX TABLE_NAME_IX1 ON TABLE_NAME (CREATEDATE);
or
CREATE INDEX TABLE_NAME_IX1 ON TABLE_NAME (CREATEDATE, PARENT, CHILD);