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

From: amonte <ax.mount@xxxxxxxxx>
To: Oracle-L Group <oracle-l@xxxxxxxxxxxxx>
Date: Thu, 24 Jan 2013 22:06:58 +0100

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


--
//www.freelists.org/webpage/oracle-l

Follow-Ups:
- RE: outer join between parent-child, not null and FK constraints
  - From: Ric Van Dyke
- Re: outer join between parent-child, not null and FK constraints
  - From: David Fitzjarrell
- Re: outer join between parent-child, not null and FK constraints
  - From: Taral Desai

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

Other related posts: