DB2 multiple column index issue

Question

If I have a multiple column index(a,b,c) and one separate index d then my query is using b,a,c,d order whether both the index will be chosen? Whether index will be choosen or not for this query?

Answer 1

In the DB2 command line, try this:

db2 => explain plan for ...insert-query-here...

In the result, you will see when indexes are used and when DB2 will use full table scans.

Answer 2

If you change it to a, b, c, d then the (a, b, c) index will be used.
If you change it to d, b, a, c (or anything starting with d), the (d) index will be chosen.
Basically, use the columns in the same order as the index you want to use.

Answer 3

A cost-based optimizer (such as DB2's) will use the statistics for the table and index objects to determine whether the (A,B,C) index or the (D) index will be better suited for a given query. The index cardinality (number of unique values in the index) is one of several data statistics gathered by the RUNSTATS command to help the optimizer estimate the relative I/O and CPU costs with each possible access path. The explain command mentioned in Aaron Digulla's answer and also the db2expln facility can help you understand which index will be chosen.