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

From: Ric Van Dyke <ric.van.dyke_at_hotsos.com>
Date: Thu, 24 Jan 2013 15:58:08 -0600
Message-ID: <C970F08BBE1E164AA8063E01502A71CF01FEE068_at_WIN02.hotsos.com>



Maybe, but then again shouldn't we be smart enough not to write such a query to begin with? :-)

There might be (and I can't think of an example right now) that you don't want it to toss out the outer join.

BTW - you have the + on the wrong side if I'm reading what your trying to say. The other join is redundant in your example even without adding the NOT NULL constraint on EMP.

You want to return everything in EMP even if there isn't a match in DEPT right? That would look like this:

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

  • Ric

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of amonte Sent: Thursday, January 24, 2013 4:07 PM To: Oracle-L Group
Subject: outer join between parent-child, not null and FK constraints

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


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

Original text of this message