Re: 10g vs 11g CBO UNNEST different results

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

On Tue, Jun 28, 2011 at 12:16 AM, Timur Akhmadeev
<Akhmadeev@xxxxxxxxxxxxxx>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@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Michael Moore
> *Sent:* Tuesday, June 28, 2011 1:32
> *To:* oracle-l@xxxxxxxxxxxxx
> *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_>
>
>

Other related posts: