Since typical outer join require all the result set from the driving table (or tables) be returned, having a not null constraint on the outer join table is immaterial, but if the same not null predicate is added to the query then it makes all the difference and oracle optimizer does pick up the right execution plan for it. 22:41:03 SQL> create table dept(deptno number not null, dname varchar2(10), primary key(deptno)); Table created. 22:41:03 SQL> 22:41:03 SQL> create table emp(empno number not null references dept(deptno), ename varchar2(10), deptno number); Table created. 22:41:03 SQL> 22:41:03 SQL> insert into dept values (1, 'A'); 1 row created. 22:41:03 SQL> insert into dept values (2, 'B'); 1 row created. 22:41:03 SQL> insert into dept values (3, 'C'); 1 row created. 22:41:03 SQL> insert into dept values (4, 'D'); 1 row created. 22:41:03 SQL> insert into emp values (1, 'A', 1); 1 row created. 22:41:03 SQL> insert into emp values (2, 'B', 2); 1 row created. 22:41:03 SQL> 22:41:03 SQL> set autotr on explain 22:41:03 SQL> 22:41:03 SQL> select ename, dname from emp, dept 22:41:03 2 where emp.deptno(+) = dept.deptno; ENAME DNAME ---------- ---------- A A B B C D Execution Plan ---------------------------------------------------------- Plan hash value: 1407862136 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 400 | 5 (20)| 00:00:01 | |* 1 | HASH JOIN OUTER | | 10 | 400 | 5 (20)| 00:00:01 | | 2 | TABLE ACCESS FULL| DEPT | 82 | 1640 | 2 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| EMP | 82 | 1640 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("EMP"."DEPTNO"(+)="DEPT"."DEPTNO") 22:41:03 SQL> 22:41:03 SQL> select ename, dname from emp, dept 22:41:03 2 where emp.deptno(+) = dept.deptno and emp.deptno is not null; ENAME DNAME ---------- ---------- A A B B Execution Plan ---------------------------------------------------------- Plan hash value: 2285423260 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 160 | 5 (20)| 00:00:01 | |* 1 | HASH JOIN | | 4 | 160 | 5 (20)| 00:00:01 | |* 2 | TABLE ACCESS FULL| EMP | 4 | 80 | 2 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| DEPT | 82 | 1640 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO") 2 - filter("EMP"."DEPTNO" IS NOT NULL) Thanks, Sai http://sai-oracle.blogspot.com -- //www.freelists.org/webpage/oracle-l