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
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
