Re: How to optimize this query?

vslabs_at_onwe.co.za (Billy Verreynne) wrote in message news:<1a75df45.0308120043.5822f78a_at_posting.google.com>...
> ctcgag_at_hotmail.com wrote in
>
> > Out of curiousity, if there's an index based on (surname, age), might
> > Oracle decide to combine a range scan on surname with a skip-scan
> > on age for only things in the right surname range?
>
> Yes. That is exactly the point. If the only index on surname is index
> (surname,age) and you do a :
> SELECT
> count(*)
> FROM t
> WHERE surname = 'SMITH'
>
> .. then you stand an excellent change of hitting an index range scan
> as Oracle will likely decide on using the (surname,age) index.
>
> Just remember though - I am _not_ saying that index range scans are
> bad. Simply that they can be bad. And that only happens when the range
> scan hits a sizeable portion of the index.
>
> If 80% of the data matches your criteria, why use an index to identify
> that 80%? Why not process the table instead? Very likely the cost of
> reading that additional 20% of table data you do not need, is _less_
> than the cost of having to read the index with a range scan.
>
> This percentage is also not fixed. It can decrease very quickly with
> the increase in size of the table and index. In which case a full
> table scan using PQ can be faster than an index range scan even on 10%
> of the data (as 80 PQs spread among 10 OPS/RAC nodes can work faster
> through the 90% non-relevant data, than a single process can range
> scan through a 10+GB index).

And just to throw a monkey-wrench (or spanner, as the case may be) into the skip-scan works, see bug 2789595.

jg

--
@home.com is bogus.
Engage!