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

[Santana]

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

[ddf]

> ---------------------------------------------------------------------------­-

> | 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

The first thing I see is a vast difference in predicate information;
for 10g (please post the complete version, all four to five numbers)
you have

1 - filter("ID"=NVL(,1))
4 - filter(2<:B1)
5 - filter(("B"."ID"=:B1 AND "B"."ID">2))

all filter operations indicating no index usage. Yet on 11g ( again
please post complete version numbers) you have

1 - access("ID"=NVL("MAX(B.ID)",1) AND "A"."ID"="ITEM_0")
4 - filter("B"."ID">2)

where the access operation usually indicates an index being used.
Also you have a hash join in 11g that's missing in 10g (which may be
due to optimizer differences between the two releases). Have you run
a 10053 trace at level 1 on both systems? Setting that event will
trace the optimizer decision 'tree' used to determine how and why the
chosen plan was implemented. Also posting table DDL and test data
would be tremendously helpful in determining the root cause of this
behavour.


David Fitzjarrell.