try this
SELECT *
FROM tests a
WHERE id = NVL ( (SELECT /*+ NO_UNNEST */ MAX (b.id)
FROM tests b
WHERE b.id > 2 AND a.id = b.id),
1);
Regards,
Mike
On Mon, Jun 27, 2011 at 12:16 PM, Santana <paulito.sant...@gmail.com> 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
>
> --
> 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
>
--
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
