Re: What index access path does INDEX

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 2 Jan 2007 10:58:09 -0000
Message-ID: <1r-dnfvuWbahpwfYRVnyugA@bt.com>

"John K. Hinsdale" <hin_at_alma.com> wrote in message news:1167677424.438720.108580_at_48g2000cwx.googlegroups.com...
> 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
>

John,

Summarizing your example:

    no hint                                        gives full tablescan
    index_ss() with named index       gives skip scan on right index

index_ss() with no index named gives full scan on 'wrong' index

I think this goes back to my original point - but only after some refinement. Bottom line - we need to know the logic behind the implementation before we can understand the symptoms we see, but how about this (as a guess):

A skip scan hint HAS to allow the optimizer to do a range or full scan on the names index. After all, if the first column has only one value to it, the skip scan is (in principle) a range scan.

Therefore, as soon as you use the index_ss() hint, it forces Oracle to cost for a skip scan on every single index - even the ones where a 'real' skip scan is impossible (and that last clause may be a bug) - because a range/full scan is only a special case of a skip scan.

If, after following this directive, the cost for an index full scan on a 'silly' index is less than the cost of using an index with a "genuine"

skip scan capability, then Oracle has to use the "silly" index.

In other words, maybe the index_ss() hint doesn't mean "do a skip scan", maybe it means "use an index - even if you have to use a skip scan to make it usable".

This type of thing can lead to other problems - in recent versions of Oracle
I've had queries crash (ORA-00600 and ORA-07445) because I've been trying to set up a combination like "use index X but don't do an index_combine
with it"

Bear in mind that, by the way, that the optimizer is only trying to model your database, so any comments like "the cost of this path was huge, but the query ran twice" is largely a reflection on the quality of the MODEL.
The path is (by definition) the right one for the model - even if it's clearly
the wrong one for the real world.

Personally I agree with the argument about reducing the number of indexes required by making the skip-scan possible. But I would only expect it to be relevant in a few special cases for very large tables with some fairly obvious candidates and with a known user-requirement in mind - and even then there may be smarter strategies to use, such as list partitioning.

As far as hints in general are concerned, see the footnote on

http://jonathanlewis.wordpress.com/2006/12/09/how-many-ways/

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Received on Tue Jan 02 2007 - 04:58:09 CST

Re: What index access path does INDEX_SS yield ?