Re: Oracle Myths

In article <1f1a539b.0205241224.32ce0824_at_posting.google.com>, you said (and I quote):
> 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)

Cool, got it. Thanks a lot. It makes sense.

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

Yup, in this case it makes absolute sense. I think it is just a special case of a range scan, as in "get the daily" stuff. My concern with something like this is when using local prefixed indexes in partitioned tables. As in very large transactional tables. The optimizer is not always smart enough. Having a "class,date" sequence actually makes the optimizer select a better path. At least in the case I can remember where something like this was used.

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

Mostly the same here. What worries me is when data entry considerations take precedence over queries. In the vast majority of databases, the volume of query access is considerably larger than the update volume. That means to me: tune the query first in terms of indexes, then use hardware redundancy (striping is an example) to reduce overhead on updates if any. Much better than the reverse.

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