Re: Oracle Myths

In article <f369a0eb.0205211119.5b4b6b90_at_posting.google.com>, you said (and I quote):
>
> There is one reason to do this: the most selective column is more likely
> to be part of the criteria of many queries. Therefore having the most
> selective column first will likely allow you to serve many queries with
> one index. As for performance, compressed index with the least selective
> column first may be better. So do what's best for your application.

I'm curious. Why do you assume that the most selective column is more likely to be part of the criteria of many queries? What says this is so?

I have yet to find one single instance where a very selective column coming first in a concatenated index makes ANY difference to data processing.

If the majority of your access is by this single column, then why use a concatenation? It's just added overhead. Create a single index on that column, period.

If not, then you MUST use the correct sequence in the index columns (less to more selective) or you simply will NOT use the index at all.

Ie, putting the most selective column first stops the concatenated index from being used for ANY queries where this column isn't specified. Which completely defeats the purpose of having that column together with another in a concatenation.

Also, I've found that most heavy volume processing (batch and reporting) is done on groups or aggregations. Which mean that the group or aggregation key MUST be first in a concatenation with ANY other keys or you will simply not be able to use the index at all.

-- 
Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam