Riyas, As we say in the states, "Bingo!" select e.ename, d.dname from emp e inner join dept d using (deptno) where d.dname = 'RESEARCH'; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=5 Bytes=90) 1 0 HASH JOIN (Cost=5 Card=5 Bytes=90) 2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=1 Bytes=11) 3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=98) alter session set optimizer_mode = all_rows; select e.ename, d.dname from emp e inner join dept d using (deptno) where d.dname = 'RESEARCH'; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=5 Bytes=90) 1 0 HASH JOIN (Cost=5 Card=5 Bytes=90) 2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=1 Bytes=11) 3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=98) alter session set optimizer_mode = first_rows; select e.ename, d.dname from emp e inner join dept d using (deptno) where d.dname = 'RESEARCH'; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=4 Card=5 Bytes=90) 1 0 NESTED LOOPS (Cost=4 Card=5 Bytes=90) 2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=1 Bytes=11) 3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=5 Bytes=35) Excellent Catch! Riyaj Shamsudeen wrote: > > *I think*, this is due to optimizer heuristics . First_rows cost model > is implemented as an heuristics model and applied over the usual cost > comparisons. I have seen optimizer choose a costlier plan because this > heuristics was/was not applied. > I wonder what results you would get if you have optimizer_mode set to > first_rows in your test case ? > Thanks > Riyaj "Re-yas" Shamsudeen > Certified Oracle DBA > ---------------------------------------------------------------- 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 -----------------------------------------------------------------