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

  • From: Saibabu Devabhaktuni <saibabu_d@xxxxxxxxx>
  • To: free <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 24 Jan 2013 23:26:08 -0800 (PST)

Alex,
I now see your point and understand the problem. In this particular case, 
oracle optimizer is not leveraging enabled and validated constraint information 
to the fullest extent in avoiding outer join. But on the other hand, besides 
somewhat fixed join order, I don't see any other downside of outer join here.

23:19:46 SQL> select ename, dname from emp, dept
23:19:57   2   where emp.deptnoÞpt.deptno(+);

ENAME      DNAME
---------- ----------
A          A
B          B

Execution Plan
----------------------------------------------------------
Plan hash value: 61568496

---------------------------------------------------------------------------
| 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| EMP  |    82 |  1640 |     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"(+))

Thanks,
 Sai
http://sai-oracle.blogspot.com

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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 01-2013