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

dont be annoyed, but
first I´d check wether there is realy the same data in both databases.

select 'ID = 1", count(id) from tests where id=1;
select 'ID = 2", count(id) from tests where id=2;
select 'ID > 2", count(id) from tests where id>2;

and also the DDL of the table.

w.r.

Am 10.07.2011 23:22, schrieb a_at_a.com:
> 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 Wed Sep 21 2011 - 09:21:53 CEST