If you go to this URL, you will find a discussion about the index_ss() hint. It’s an interesting example of how (a) you could argue that Oracle is ignoring hints until (b) you realise that we really can’t tell what’s going on because we don’t know what a hint is supposed to do.
In summary – Spendius asks why an index_ss() hint is making the optimizer pick an index full scan, and I confirm that I can create a (slightly unusual) example where a query does a full tablescan by default, but does an index full scan when hinted with a skip scan hint – and in my case the whole thing looks like a bug anyway.
Spendius supplies more details about his example which shows it isn’t my special case, but suggests (a) that Oracle is obeying the hint but (b) still manages to find the full scan more attractive. So I proposed a hypothesis about why this behaviour could be the specified action for index_ss().
John Hinsdale then followed up with an example where the optimizer uses a tablescan when unhinted, uses a skip scan of the appropriate index if the index name is specified in the hint, but uses a full scan of “the wrong index” if the hint omits any index names.
This leads to the more refined hypothesis that, from the optimizer’s perspective, a range scan or full scan is just a special case of a skip scan (i.e. a skip scan where the first column(s) hold only one distinct value, or a skip scan skipping zero columns) and the index_ss() hint currently means (roughly):
Evaluate the use of the named index (or all indexes if none is named) for sequential access even if it means employing a skip scan to make the index usable; then use the cheapest path – even if it isn’t a skip scan.
John Hinsdale’s closes with the comment:
This explanation is both plausible and enlightening.
That may be true, but it doesn’t mean that the hypothesis is right – even though we might decide to work with it until some other phenomenon shows it to be wrong.
Footnote (4th Jan):
Examination of the “Single Table Access Path” section of the 10053 trace file for a few test cases indicates that if you give Oracle the index_ss() hint naming an index for which a skip scan is inappropriate (i.e. the column in the predicate is the leading column or simply doesn’t appear in the index) then the optimizer will try a range or full scan on that index. Consequently, if you don’t specify any indexes in the hint, the optimizer will test all indexes that are legal for range, full, or skip scans – depending on the definition of the indexes and the nullity of the columns.
This means that it shouldn’t “convert” a skip scan into a full scan if the index has been named and is legal; and I still haven’t been able to make Oracle do an index full scan of the index named in the index_ss() hint – which is what the original poster was complaining about.