Re: 10g vs 11g CBO UNNEST different results

From: coskan gundogar <coskan_at_gmail.com>
Date: Tue, 28 Jun 2011 14:07:17 +0100
Message-ID: <BANLkTi=5DvgdOaKPu-TGw3Svd0gcWGvZOA_at_mail.gmail.com>

What are your versions ?

SQL> select * from v$version
  2 ;

BANNER

---

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production

SQL> _at_test
SQL> select * FROM tests a
  2 WHERE id = NVL ( (SELECT MAX (b.id)

  3                       FROM tests b
  4                      WHERE b.id > 2  AND a.id = b.id),
  5                   1);

        ID
----------
         1

On 27 June 2011 22:32, Michael Moore <michaeljmoore_at_gmail.com> wrote:

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

-- 
-- 
Coskan GUNDOGAR

Oracle DBA

Email: coskan_at_gmail.com
Blog: http://coskan.wordpress.com
Twitter: http://www.twitter.com/coskan
Linkedin: http://uk.linkedin.com/in/coskan

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jun 28 2011 - 08:07:17 CDT