outer join between parent-child, not null and FK constraints

From: amonte <ax.mount_at_gmail.com>
Date: Thu, 24 Jan 2013 22:06:58 +0100
Message-ID: <CABV7K9tc1vCci047qgEBGLnRrGBrm6p5YR+8VtMoqRWePihDsQ_at_mail.gmail.com>



Hello all
As a simple example we will use tables emp and dept (the demo tables) who have a parent child relationship, if we make emp.deptno NOT NULL and we run this query

select ename, dname from emp, dept
 where emp.deptno = dept.deptno(+)

shouldnt Oracle be clever enough know that the outer join is redundant?

The explain plan shows:



| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 14 | 588 | 7 (15)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |    14 |   588 |     7  (15)| 00:00:01 |

| 2 | TABLE ACCESS FULL| EMP | 14 | 280 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 88 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):


   1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO"(+))

emp can never return rows which doesnt have a match in dept so outer join seems redundant with the constraints info?

Alex

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jan 24 2013 - 22:06:58 CET

Original text of this message