Re: Oracle Myths

ricew_at_operamail.com (William Rice) wrote in message news:<1f1a539b.0205241224.32ce0824_at_posting.google.com>...
> <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...

Yes, I firmly believe that understanding your application should be one of the first steps towards effective tuning. As for having the most selective column first, the benefit is more obvious in the OLTP world where one of the basic business requirements is fast query response and short result sets. Examples are customer service, flight reservation, etc. As your application evolves more screens will be added but guess what gives you a better chance of meeting this requirement with existing indexes? In fact I haven't come across many queries where having less selective columns first had an advantage.

In the DSS world where you can run all types of summarization/aggregation and many queries involve table/index scans, you may find more cases which support the counter argument.

>
> Will
Received on Sun Jun 02 2002 - 21:23:21 CDT