Re: 10g vs 11g CBO UNNEST different results

From: Michael Moore <michaeljmoore_at_gmail.com>
Date: Tue, 28 Jun 2011 07:20:02 -0700
Message-ID: <BANLkTikTCEKHTvFd4AGnwD7HNNK_RYq09w_at_mail.gmail.com>



Awesome, I will provide info to our DBA and developer team.

On Tue, Jun 28, 2011 at 12:16 AM, Timur Akhmadeev <Akhmadeev_at_netcracker.com>wrote:

> Hi
>
>
>
> It’s a “feature” 7215982 “unnest subquery embedded inside an expression”.
> Turning it off with the _fix_control will fix it.
>
>
>
> Regards
>
> Timur Akhmadeev
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Michael Moore
> *Sent:* Tuesday, June 28, 2011 1:32
> *To:* oracle-l_at_freelists.org
> *Subject:* 10g vs 11g CBO UNNEST different results
>
>
>
> create table tests (id number);
>
>
>
> insert into tests values(1);
>
> insert into tests values(2);
>
> commit;
>
>
>
> /* in 10g this gives 1 row, in 11g no rows */
>
> SELECT *
>
> FROM tests a
>
> WHERE id = NVL ( (SELECT MAX (b.id)
>
> FROM tests b
>
> WHERE b.id > 2 AND a.id = b.id),
>
> 1);
>
>
>
> /* the hint will make 11g have same result as 10g */
>
> SELECT *
>
> FROM tests a
>
> WHERE id = NVL ( (SELECT /*+ NO_UNNEST */ MAX (b.id)
>
> FROM tests b
>
> WHERE b.id > 2 AND a.id = b.id),
>
> 1);
>
>
>
> I got this example from the oracle-plsql group.
>
> Is this an oracle bug?
>
>
>
> Regards,
>
> Mike
>
>
>
> ------------------------------
> The information transmitted herein is intended only for the person or
> entity to which it is addressed and may contain confidential, proprietary
> and/or privileged material. Any review, retransmission, dissemination or
> other use of, or taking of any action in reliance upon, this information by
> persons or entities other than the intended recipient is prohibited. If you
> received this in error, please contact the sender and delete the material
> from any computer.
>
>
>
> <#130d51b3f0e9972c_>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jun 28 2011 - 09:20:02 CDT

Original text of this message