Re: What index access path does INDEX_SS yield ?

"joel garry" <joel-garry_at_home.com> wrote in message news:1167952224.887350.7410_at_38g2000cwa.googlegroups.com...
>
> Jonathan Lewis wrote:
>> 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.
>
> Bugs. That kind of negates this whole paragraph, doesn't it?

    Not at all.

    Would you say "Oracle ignores SQL" because there are bugs     in the SQL which give you incorrect answers ?

    I would prefer people to say "There are a few bugs with hints"     rather than saying "Oracle ignores hints". If they adopt the former     belief, then they might pause to thing "Maybe I've made a mistake     in my hints" a little more readily.

    Think how many times people on this newsgroup have said     "Oracle has a bug" and then produced a piece of (unhinted)     SQL that shows they don't understand how SQL works.     Very few of them said things like "Oracle is ignoring my     subquery" - and I don't think I've ever seen anyone reply     "that's because Oracle can ignore subqueries". (Though     they may be true in 10gR2 occasionally).

>>
>> 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.
>>
>
> I believe this thread answers that, and the answer to Spendius'
> original question is "yes, changing the relative volumes of data
> can affect the path chosen even with a hint."
>

1. The thread doesn't answer it - Spendius hasn't supplied enough data for a complete analysis - it may be a bug, it may be a typing error - it may be expected behaviour.
2. That wasn't his question - but your answer is wrong. Apart from bugs, changes in volume, relative volume, and distribution of data will NOT change the path in the face of a complete set of hints.

> Somehow I wonder if we are stumbling around a strangeness
> with global indices here.

    It's quite common for bits of code to play catch-up in Oracle.     New features are quite complete when launched - so it's quite     possible that some pieces of optimizer code don't respond     correctly to new optimisation strategies or their associated     hints. However, this was one of the options I hacked through     when trying to check what was going on - and nothing in the     trace file suggested a missing code path.

Spendius -

    If you care to email me the full trace 10053 file of the     misbehaving execution, I'll see if I can determine what     the problem is.

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