Re: Different results for the same query in 10g and 11g

Try:

select *
from tests a
where a.id in
(

	select nvl(max(b.id),1) id
	from tests b
	where b.id>2
		and a.id=b.id

even I don't understand the "b.id>2" thing nor the "a.id=b.id".

On 27.6.2011. 21:17, Santana wrote:
> 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 Sun Jul 10 2011 - 23:22:56 CEST