Re: outer join between parent-child, not null and FK constraints
From: Saibabu Devabhaktuni <saibabu_d_at_yahoo.com>
Date: Thu, 24 Jan 2013 23:26:08 -0800 (PST)
Message-ID: <1359098768.71252.YahooMailNeo_at_web161303.mail.bf1.yahoo.com>
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.
Plan hash value: 61568496
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 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 |
Date: Thu, 24 Jan 2013 23:26:08 -0800 (PST)
Message-ID: <1359098768.71252.YahooMailNeo_at_web161303.mail.bf1.yahoo.com>
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
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jan 25 2013 - 08:26:08 CET