Re: What index access path does INDEX_SS yield ?

Jonathan Lewis wrote:

> I don't know why the optimizer has decided to do this - but perhaps
> part of the algorithm for skip scans converts them to full scans if
> the cost exceeds some limit. I'd take this up with Oracle.

Yah, Oracle seems to do some weird things when one attempts via hints to "force" it to use skip-scan (an approach that strikes me as dubious; see below).

For example, consider the small 10-row table HR.JOB_HISTORY in the example "HR" schema that ships w/ Oracle 10.2.0.1.0:

http://otb.alma.com/otb.fcgi?func=btable&server=orcl&user=HR&table=JOB_HISTORY

This tables comes with a compound index (the PK actually) on the key (EMPLOYEE_ID, START_DATE), as well as some other single-column indexes:

http://otb.alma.com/otb.fcgi?func=tinfo&server=orcl&user=HR&table=JOB_HISTORY

A simple query that filters on START_DATE does the full scan on the tiny table, as expected:

http://otb.alma.com/otb.fcgi?func=sqlexplain&server=orcl&user=HR&qid=21

Adding the INDEX_SS hint, fully qualified with the exact index to use, causes Oracle to perform the skip-scan on the index as instructed:

http://otb.alma.com/otb.fcgi?func=sqlexplain&server=orcl&user=HR&qid=19

Now for the weird part: if you give the INDEX_SS hint, to include the table range alias (which is unambiguous here -- only one table in the FROM clause), but not specifying the exact index, Oracle constructs an execution plan that does an index full-scan, but on one of the single column indexes (on EMPLOYEE_ID) which is a column completely irrelevant to this query, at least as far as row selection and selectivity is concerned:

http://otb.alma.com/otb.fcgi?func=sqlexplain&server=orcl&user=HR&qid=20

Very strange.

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Stepping back, it strikes me that the very idea of forcing a skip-scan via a hint is somewhat dubious in general. Skip-scan seems like the sort of obscure access method the optimizer might employ on its own, when it is very sure of what it is doing (i.e., that the number if "skips" is known, and small) but that, when forced upon the optimizer by the end user can, potientally, anyway, lead to unpredictable results -- while predictiability is precisely the thing hints are good for, in their place as a last resort.

I wonder if Oracle includes the INDEX_SS hint more as a matter of completeness, so that it can guarantee that every access method that could be used by the optimizer on its own is also available for forcible use (and abuse) by a query writer.

As another example, the top hit on a Google search for INDEX_SS gives an example provided by the PSOUG organization:

    http://www.psoug.org/reference/hints.html

When I EXPLAIN the example query (again, w/ Oracle's tiny test data as shipped), the hint increases the estimated cost from 1 (for the full table scan) to 102 (w/ the index skip-scan), and when I run the query in the two forms, the actual cost rises from 2 consistent gets to 4.

I've read elsewhere that INDEX_SS can be good for leveraging a compound index when one is very, very tight for space, but I have trouble envisioning a situation when it's space savings would be worth it.

Happy optimizing,

        John Hinsdale Received on Mon Jan 01 2007 - 13:07:44 CST