Re: What index access path does INDEX_SS yield ?

"John K. Hinsdale" <hin_at_alma.com> wrote in message news:1168050283.611090.194630_at_s80g2000cwa.googlegroups.com...
>
>> Jonathan Lewis wrote:
>> > 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".
>
> DA Morgan wrote:
>> Your last comment caught my interest. Would you have an example
>> of Oracle ignoring a subquery you could share?
>
> I doubt this is the interesting example Dan is looking
> for, but a very trivial degenerate case, using the demo
> "SH" (Sales History) schema shipped w/ 10g:
>
> SELECT *
> FROM customers
> WHERE 1 = 1
> OR cust_id IN ( SELECT cust_id
> FROM customers
> WHERE cust_id = 100
> )
>
> will do a full scan and ignore the subquery completely.
> I imaginge there is a more interesting case where Oracle
> will decide a subquery doesn't affect the results and
> can be optimized out. I presume that is the only time
> the optimizer is free to ignore them?
>
> Here is proof from a running instance that SQL above
> optimizes into the full scan as claimed:
>
> http://otb.alma.com/otb.fcgi?func=sqlexplain&server=orcl&user=SH&qid=23
>
> A more interesting example -- or general description of
> how/when the phenomemon occurs, would be enlightening.
>
> Cheers,
>
> John Hinsdale
>

John,

Your final point is correct - in 10gR2 the optimizer will attempt to eliminate anything which is redundant provided it is guaranteeably going to produce the same result set. Your example is actually a case of predicate elimination - the execution plan will show no filter predicates, despite the two that exist in the original text.

I was thinking (only theoretically) of the fact that 10gR2 can eliminate joins - and since subqueries can be transformed to join there will be examples you could construct where a subquery disappears because it is first transformed and then eliminated. (I haven't yet done this - but given the clue I'm sure you or Dan will be able to create a case very quickly - the critical test is that it should be a join on a unique key that is guaranteed not to eliminate data).

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