Re: How to optimize this query?

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

--
Billy