Re: Oracle Myths

<SNIP>
> 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 agree completely

>
> 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.

I firmly agree than using the most selective entry for the first column of an index is not always the best choice. I just disagree with there not being a single instance...

Case 1.
Maybe lookups are generally on serial_no(serial_no being the physical items serial_no), but the primary key is material_code, serial_no. No need to have an index on both, and by making the more selective column the first entry, you don't end up creating two indexes. If I create the index only on invoice_no, joins to this table will be less then optimal.(This is not a real world example, didnt have a system to look at for which this would easily apply)

Inserts will be faster because I am not maintaining two indexes...

Case 2.
  Records with time_stamp(year to day), classification, and a bunch of other data. Users usually query on a date range. Classification is the least selective(as there are only 50ish) and is often(but not always) part of their query. An index on classification isn't so useful because it barely cuts down the data by a high enough percentage to make it worth going through the index.(This is a real world example)

I just stick with looking at the queries, and forming my indexes based on the query criteria...

Will Received on Fri May 24 2002 - 15:24:49 CDT