CODEWITHSUNDEEP
db2
Serge
Serge

Reputation: 626

DB2 indexing setup

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.

Upvotes: 0

Views: 68

Answers (1)

Charles
Charles

Reputation: 23793

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:

http://www-304.ibm.com/partnerworld/wps/servlet/ContentHandler/stg_ast_sys_wp_db2_i_indexing_methods_strategies

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

Upvotes: 2

Related Questions