Jonathan Lewis wrote:
> "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).
Thanks Jonathan and John.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Sat Jan 06 2007 - 15:12:54 CST
