I am looking through some table source code, and I'm not sure how indexes work. Here is the example I see.
CREATE INDEX INDEXNAME ON AWARD ( AWARD_ID, CUST_ID );
I don't understand what the parameter values mean. Is the index for each individual column, or is it combined together to become an index.
It's an index that contains two fields. Indexes in general are used for Selection, Joining, Grouping and/or Ordering.
The key thing to realize is that a multi column index is useful from left to right.
For selection, such an index would be quite useful if you had a where clause that looked like
WHERE AWARD_ID = 123 AND CUST_ID = 456
If would also be helpful for
WHERE AWARD_ID = 123
But probably not (directly) helpful for
WHERE CUST_ID = 456
Since the leftmost column of the index (AWARD_ID) is not referenced.
Joining works in a similar manner.
--index useful
FROM TBLA JOIN AWARD USING (AWARD_ID,CUST_ID)
or
FROM TBLA JOIN AWARD USING (AWARD_ID)
--index NOT useful
FROM TBLA JOIN AWARD USING (CUST_ID)
as do ordering and grouping.
If you happen to be using DB2 for IBM i, the following paper is an awesome resource:
If DB2 LUW, the parts about bitmap indexs probably apply, but ignore the information about EVI indexes. Also look at the planning and performance sub-sections Indexes section in the DB2 LUW infocenter http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.admin.perf.doc/com.ibm.db2.luw.admin.perf.doc-gentopic8.html
