Re: 10g vs 11g CBO UNNEST different results
- From: coskan gundogar <coskan@xxxxxxxxx>
- To: michaeljmoore@xxxxxxxxx
- Date: Tue, 28 Jun 2011 14:07:17 +0100
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> @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@xxxxxxxxx> 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@xxxxxxxxx
Blog: http://coskan.wordpress.com
Twitter: http://www.twitter.com/coskan
Linkedin: http://uk.linkedin.com/in/coskan
Other related posts:
