Hi,
I am trouble shooting a performance problem.
I run a similar query against two different tables, both have a compound primary key and in both this compound key is indexed.
The columns that are the compound primary key and the index are:
ClassId and Index.

The 1st Query is

SELECT CLASSID, INDEX, HEIGHT, WEIGHT, AGE FROM PERSON
WHERE CLASSID = 5 ORDER BY CLASSID, INDEX;

The 2nd Query is against EMPLOYEE and is

SELECT CLASSID, INDEX, HEIGHT, WEIGHT, AGE FROM EMPLOYEE
WHERE CLASSID = 6 ORDER BY CLASSID, INDEX;;

When I look at the explain plans, one query uses the index the other does not.
My question is if a index is a combination of columns, when is that index actually used?
Is it when
-both columns are specified in the where clause?
-both columns are specified in the orderBy clause?
-when one of the two columns is specifed in the where clause?
-when one of the two columns is specifed in the order by clause?
or
-all the time as it is also the primary key?

Any help greatly appreciated!

Neither of these statements is correct. The right way to put it would be "An index, whether compound or not, is used when the optimizer thinks it is beneficial".

There are 2 kinds of index usage:

1. B-tree access using the non-leaf pages to quickly locate the leaf pages with the corresponding RID's. This is what most people think of when they say "index access".

2. Scanning all the leaf pages without any access to the non-leaf pages. This is like a tablespace scan of the the entire index.

To use access type number 1 above with a composite index, then at least the left most column(s) of the multi-column index must be supplied in the predicate (WHERE Clause). Otherwise, DB2 will not use the index, or may use access type 2 above.

Thanks for your replies.
It appears that if the table has no data an index scan is used, but if the table has data an index scan is not used. Any comments appreciated.

DB2 will usually do a table scan if the number of rows is relatively small, because it is faster than using an index. This assumes runstats has been run and DB2 knows the number of rows in the table. A new table may have stats set to -1, which means that runstats has not been run.

If you alter the table to volatile, then DB2 will ignore the stats and is more likely to use an index if a suitable index is available.