How does Index work exactly in DB2?

[fletchsod]

Since the Index work different on every of the database software. So, I'm posting a simple question here hoping for an answer.

Let's look at the WHERE clause in the SQL Query.

#1 --> SELECT * FROM REPORTS WHERE CUSTOMER_ID = 'blah'
#2 --> SELECT * FROM REPORTS WHERE USER_ID = 'blah'
#3 --> SELECT * FROM REPORTS WHERE CUSTOMER_ID = 'blah' and USER_ID = 'blah'

As you can see, if I create an index for #3 to both the CUSTOMER_ID and USER_ID for quicker response.

The question is do I have to make a sperate index for #1 and #2. (For #1 --> create index for CUSTOMER_ID and a seperate index for USER_ID while the #3 use a seperate index). Or would the #1 and/or #2 still work quickly by taking advantage of the index that was made for #3?? (Yea, may be a bit slower but faster than the one without an index).

Thanks...

P.S. Is there a better documentation somewhere on this one??

 

[blom0344]

Start by creating an explain plan with no indices in place.
Then create index #3 and see if and when part of the path switches from a full table scan to an index scan.


Ties Blom

 

[ddiamond]

If you had a combined index (Customer_id, User_Id), queries #1 and #3 can take advantage of it, but query #2 could not. If you instead have two seperate indexes customer_id and user_id, query #3 would choose which one to use, but it can only use one of the indexes (can only use one index per table). For best results I would recomend a combined index on (customer_id, user_id) and an index on just user_id. The combined index would take care of queries #1 and #3, and the single index would take care of query #2.

 

[fletchsod]

Okay... I get the drift on the index usages. I hope I can do it properly along the way with more indexes...

Thanks..