Re: Newbie--indexes

Science vs. Art

Index utilization under some circumstances comes down to these 2 basics. However, for your 2 table join, you should be able to dictate your index utilization. Before I 'attempt' to address your issue specifically, you should make time to make 'explain plan' your friend. As you may or may not know, Explain Plan informs the analyst of the Execution path Oracle will utilize in performing your SQL script. The optimizer you are using, RULE vs. COST will have some bearing on the results as well. As an aside to an aside...under v7.1.6 the COST optimizer STILL does not perform as well (if at all) in large queries/databases. Refrain from jumping on the band-wagon as 90% of Oracle employees will tell you it works...It does Not...yet.

Moving on. Another tidbit to make note of is the table ordering within your FROM clause. The last table listed in your FROM clause should be considered your driving table. For example, let's assume the smaller of your two tables is called salesman and it has 1000 rows and it has an index on Department ID. Let's also assume your 1,000,000 row table consists of a salespeople ID, customer ID, and cumulative sales where salesperson ID has a concatenated index of (salesperson_id, customer_id)

select sum(sls.sales)
from sales sls, salespeople sls_ppl
where

     sls_ppl.department_id = 10
and sls_ppl.salesperson_id = sls.salesperson_id;  

Notice the table salespeople table falls last in the FROM clause. An Explain plan (Using RULE) would indicate that you will be using the index on Department ID to perform it's search against the salepeople table. Subsequently, the explain plan will indicate that it intends on using the concatenated index to begin it's search against your large table. Why ? Because the salesperson_id is on the leading edge of the index, so it can. If the concatenated index began with customer ID and ended with salesperson ID, you would not be able to us the index at all, therefore a full-table scan would occur and you would sleep.

This may be a horrible example as it is early Monday morning and I'm using this time to wake as well as attempt to provide valuable information. The bottom line is if you understand how and when Oracle utilizes indexes, then you can say that you've covered the Science. Knowing when to use an index, how to specify/override an index in your query, and how to disable it, comes when you become intimately aware of your data's distribution and how best to retrieve it...this is the Art.

Best of luck.

-D

Darryl Snedeker 		ph# (512) 602-2168
Senior Database Administrator	fax (512) 602-5018
Advanced Micro Devices		Austin, Texas