Different results for the same query in 10g and 11g
Date: Mon, 27 Jun 2011 13:09:42 -0700 (PDT)
Message-ID: <5ab9936f-80b0-4117-8bd3-366952d98ac3_at_a31g2000vbt.googlegroups.com>
Hi all.
I need your help for the following query that return one row in 10g and dont return any row in 11g: I know which ther is others ways to implement
this query but i wan understand what is wrong!
This is mysterious query :
select *
from tests a
where id=nvl(( select max(b.id)
from tests b where b.id>2 and a.id=b.id ), 1)
What i must do in 11g in order this query return data ?
There is all script and the execution plan for 10g and 11g:
create table tests (id number)
insert into tests values(1)
insert into tests values(2)
select *
from tests a
where id=nvl(( select max(b.id)
from tests b where b.id>2 and a.id=b.id ), 1)
in 10g return one row and this is the exection plan:
PLAN_TABLE_OUTPUT
SQL_ID 66rp53rd4493w, child number 0
select * from tests a where id=nvl(( select max(b.id)
from tests b where b.id>2 and a.id=b.id ), 1)
Plan hash value: 2928053570
| Id | Operation | Name | E-Rows |
|* 1 | FILTER | | |
| 2 | TABLE ACCESS FULL | TESTS | 2 |
| 3 | SORT AGGREGATE | | 1 |
|* 4 | FILTER | | | |* 5 | TABLE ACCESS FULL| TESTS | 1 | -----------------------------------------------
Predicate Information (identified by operation id):
1 - filter("ID"=NVL(,1)) 4 - filter(2<:B1) 5 - filter(("B"."ID"=:B1 AND "B"."ID">2))
in 11g dont return any row and this is the execution plan:
PLAN_TABLE_OUTPUT
SQL_ID 66rp53rd4493w, child number 0
select * from tests a where id=nvl(( select max(b.id)
from tests b where b.id>2 and a.id=b.id ), 1)
Plan hash value: 848999739
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-
Mem |
| 0 | SELECT STATEMENT | | | |
| |
|* 1 | HASH JOIN | | 1 | 899K| 899K| 207K (0)|
| 2 | VIEW | VW_SQ_1 | 1 | |
| |
| 3 | HASH GROUP BY | | 1 | 1001K|
1001K| | |* 4 | TABLE ACCESS FULL| TESTS | 1 | |
| |
| 5 | TABLE ACCESS FULL | TESTS | 2 | |
| |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
1 - access("ID"=NVL("MAX(B.ID)",1) AND "A"."ID"="ITEM_0") 4 - filter("B"."ID">2)
Regards,
Paulito Santana
Received on Mon Jun 27 2011 - 15:09:42 CDT