> When trying out hints I only focused on no_unnest and totally forgot about > no_merge which should have been the more obvious one. That is consistent > with the result I got using the rownum pseudo-column which also prevents > merging. NO_MERGE causes CBO to be used and under CBO the query works correctly (even without NO_MERGE) as Connor pointed out. If you set _complex_view_merging to false, then the query works ok under RBO as well... SQL> select * from ( select distinct deptno from ( select deptno, job, 2 min(sal) from scott.emp group by deptno, job ) where deptno = 20 ) 3 / DEPTNO ---------- 20 20 20 SQL> alter session set "_complex_view_merging"=false; Session altered. SQL> select * from ( select distinct deptno from ( select deptno, job, 2 min(sal) from scott.emp group by deptno, job ) where deptno = 20 ) 3 / DEPTNO ---------- 20 Tanel. ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------