Re: What index access path does INDEX_SS yield ?

"sybrandb" <sybrandb_at_gmail.com> wrote in message news:1167474399.766259.93840_at_48g2000cwx.googlegroups.com...
>
> Spendius wrote:
>> Hi,
>> I have a query where the skip scan hint keeps the optimiser
>> choosing an index full scan: will the use of this hint always
>> have this effect ? This is a 10g DB.
>>
>> Thanks.
>> Spendius
>
> Generic questions without any details as to the specific statement,
> without running the statement with event 100053 set: it is always the
> same with you, isn't it?
> Do you really think such sweeping generic answers are possible?
> If not, why do you continue to post questions without any information?
> Or are you just searching for silver bullets?
> Hints are just what they are: HINTS.
> There must be a specific reason why CBO choose to ignore the hint.
> Set event 10053 (as explained here many times before) and find out the
> reason.
>
> --
> Sybrand Bakker
> Senior Oracle DBA
>

Sybrand,

It has also been pointed out to you several times that Oracle "hints" are not just hints in the English-language sense of the word. In the absence of bugs, and provided they are syntactically correct and legal, they must be obeyed.

The last time you made your erroneous claim, I asked you for an example that demonstrated your point - I am still waiting for an answer.

Spendius -
Which version of Oracle, 10g is not a sufficient.indicator. I have seen some odd behaviour with index_ss hints in 10.1.0.2.

Apart from that, I do have one case (last tested 10.2.0.1) where a query switches from a tablescan to an index full scan when given the index skip scan hint.

But the index is a reverse key index and I think this is an optimizer bug, as the index full scan cost is less than the tablescan cost and should have been chosen by default. Moreover, the skip scan cost is not calculated when the skip scan hint is used.

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