10g vs 11g CBO UNNEST different results
- From: Michael Moore <michaeljmoore@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 27 Jun 2011 14:32:27 -0700
create table tests (id number);
insert into tests values(1);
insert into tests values(2);
commit;
/* in 10g this gives 1 row, in 11g no rows */
SELECT *
FROM tests a
WHERE id = NVL ( (SELECT MAX (b.id)
FROM tests b
WHERE b.id > 2 AND a.id = b.id),
1);
/* the hint will make 11g have same result as 10g */
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);
I got this example from the oracle-plsql group.
Is this an oracle bug?
Regards,
Mike
Other related posts:
