Oracle Scratchpad

January 3, 2007

Hints and Guesses

Filed under: Hints,Ignoring Hints,Indexing — Jonathan Lewis @ 8:45 pm BST Jan 3,2007

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. 

[Further reading on "ignoring hints"]

1 Comment »

  1. [...] may recall I wrote a note about a problem that someone was having with the index skip scan hint, and followed it up with a note on my website about the resulting 10053 trace file concluding with [...]

    Pingback by Index Combine « Oracle Scratchpad — March 5, 2007 @ 5:49 pm BST Mar 5,2007 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,173 other followers