Re: outer join between parent-child, not null and FK constraints
From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Thu, 24 Jan 2013 16:03:42 -0800 (PST)
Message-ID: <1359072222.86576.YahooMailNeo_at_web121605.mail.ne1.yahoo.com>
That is interesting as your posted plan matches the plan I generated without the constraints. In the second plan the hash join outer is gone, replaced by a merge join outer so I was incorrect in stating the outer join was gone. I think the logic of your argument may need some work; you are asking for an outer join and Oracle can't simply eliminate that by virtue of what an outer join does and what it returns. Reversing the outer join operator still generates an outer join step even though there are no records in EMP that do not match records in DEPT by virtue of the NOT NULL constraints:
SQL> select ename, dname
2 from emp, dept
3 where emp.deptno = dept.deptno (+);
ENAME DNAME
---------- --------------
ADAMS RESEARCH
ENAME DNAME
---------- --------------
14 rows selected.
Execution Plan
Plan hash value: 1330733389
Predicate Information (identified by operation id):
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO"(+))
Statistics
1 recursive calls
0 db block gets
25 consistent gets
0 physical reads
0 redo size
756 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
SQL>
There is a record in DEPT that won't match EMP as demobld.sql creates and loads the tables. Remember that an outer join returns all values from one table whether or not they match keys in the other table so Oracle can't simply say 'well, gee, this is a foreign key they MUST match exactly' even with a NOT NULL constraint in place.
My goal was not to confuse anyone with my response. My apologies if I did and I hope this clarifies my thoughts on the matter.
From: amonte <ax.mount_at_gmail.com>
To: David Fitzjarrell <oratune_at_yahoo.com> Cc: Oracle-L Group <oracle-l_at_freelists.org> Sent: Thursday, January 24, 2013 4:13 PM Subject: Re: outer join between parent-child, not null and FK constraints
Date: Thu, 24 Jan 2013 16:03:42 -0800 (PST)
Message-ID: <1359072222.86576.YahooMailNeo_at_web121605.mail.ne1.yahoo.com>
That is interesting as your posted plan matches the plan I generated without the constraints. In the second plan the hash join outer is gone, replaced by a merge join outer so I was incorrect in stating the outer join was gone. I think the logic of your argument may need some work; you are asking for an outer join and Oracle can't simply eliminate that by virtue of what an outer join does and what it returns. Reversing the outer join operator still generates an outer join step even though there are no records in EMP that do not match records in DEPT by virtue of the NOT NULL constraints:
SQL> select ename, dname
2 from emp, dept
3 where emp.deptno = dept.deptno (+);
ENAME DNAME
---------- --------------
SMITH RESEARCH ALLEN SALES WARD SALES JONES RESEARCH MARTIN SALES BLAKE SALES CLARK ACCOUNTING SCOTT RESEARCH KING ACCOUNTING TURNER SALES
ADAMS RESEARCH
ENAME DNAME
---------- --------------
JAMES SALES FORD RESEARCH MILLER ACCOUNTING
14 rows selected.
Execution Plan
Plan hash value: 1330733389
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 308 | 6 (0)| 00:00:01 | | 1 | NESTED LOOPS OUTER | | 14 | 308 | 6 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL | EMP | 14 | 126 | 3 (0)| 00:00:01 || 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 | |* 4 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 1 (0)| 00:00:01 |
Predicate Information (identified by operation id):
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO"(+))
Statistics
1 recursive calls
0 db block gets
25 consistent gets
0 physical reads
0 redo size
756 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
SQL>
There is a record in DEPT that won't match EMP as demobld.sql creates and loads the tables. Remember that an outer join returns all values from one table whether or not they match keys in the other table so Oracle can't simply say 'well, gee, this is a foreign key they MUST match exactly' even with a NOT NULL constraint in place.
My goal was not to confuse anyone with my response. My apologies if I did and I hope this clarifies my thoughts on the matter.
David Fitzjarrell
From: amonte <ax.mount_at_gmail.com>
To: David Fitzjarrell <oratune_at_yahoo.com> Cc: Oracle-L Group <oracle-l_at_freelists.org> Sent: Thursday, January 24, 2013 4:13 PM Subject: Re: outer join between parent-child, not null and FK constraints
Hi David
I added the constraints already before post the mail!
In your example it did Merge Join Outer, why you say it is smart enough to not outer join?
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Fri Jan 25 2013 - 01:03:42 CET