Indexing Urgent

[Kumar_RP]

Hai,
Anyone can let me know about the sequence of indexing

For example 10 columns in my table of which 4 columns are indexed...
Col1,....Col10

Indexed columns are Col1, Col2, Col3, and Col4

Which should be the order in my where clause....which combination is more advisable in performance tuning..

write back to

Palani Kumar.R

palanir@sakinfotech.co.in

[dknight]

In general, you should structure your where clauses to use the indexes. Then you should structure them so that the greatest amount of screening is done first.

Best of all, you should use EXPLAIN PLAN and determine the optimal structuring that way.

Good luck.

[Kumar_RP]

Hai Dave,
Thanx for your reply..

I tried for Explain Plan also... I am not able to find out which sequence of indexing is advisable for performance tuning concern...


So, I want to know about which sequence of indexing will be helpful in Where clauses.... that is we have to start from greatest result screening or smallest screening....

[dknight]

There is a tool or a setting that does this, but I cannot remember. It might be the rule based optimizer. You can check the DBA forum.

The basic idea is that you want to get rid of as much data as soon as possible. I think you know this, so pardon this wordy example.

You want to locate males in a certain city. The city data is distributed across 20 cities with the largest city having 15% of the sample. The sex data is distributed across male and female equally. In this case, you could screen on sex and get rid of 50% of the data where as city may only screen 15%.

The conclusion is that you need to know your data!

Good luck.