Re: What index access path does INDEX_SS yield ?

> 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 Received on Fri Jan 05 2007 - 20:24:43 CST