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

From: Saibabu Devabhaktuni <saibabu_d_at_yahoo.com>
Date: Thu, 24 Jan 2013 22:47:52 -0800 (PST)
Message-ID: <1359096472.72666.YahooMailNeo_at_web161305.mail.bf1.yahoo.com>

Since typical outer join require all the result set from the driving table (or tables) be returned, having a not null constraint on the outer join table is immaterial, but if the same not null predicate is added to the query then it makes all the difference and oracle optimizer does pick up the right execution plan for it. 22:41:03 SQL> create table dept(deptno number not null, dname varchar2(10), primary key(deptno));

Table created.

22:41:03 SQL>
22:41:03 SQL> create table emp(empno number not null references dept(deptno), ename varchar2(10), deptno number);

Table created.

22:41:03 SQL>
22:41:03 SQL> insert into dept values (1, 'A');

1 row created.

22:41:03 SQL> insert into dept values (2, 'B');

1 row created.

22:41:03 SQL> insert into dept values (3, 'C');

1 row created.

22:41:03 SQL> insert into dept values (4, 'D');

1 row created.

22:41:03 SQL> insert into emp values (1, 'A', 1);

1 row created.

22:41:03 SQL> insert into emp values (2, 'B', 2);

1 row created.

22:41:03 SQL>
22:41:03 SQL> set autotr on explain
22:41:03 SQL>
22:41:03 SQL> select ename, dname from emp, dept
22:41:03   2   where emp.deptno(+) = dept.deptno;

ENAME      DNAME
---------- ----------

A          A
B          B
           C
           D

Execution Plan

---

Plan hash value: 1407862136

---

| 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| DEPT |    82 |  1640 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    82 |  1640 |     2   (0)| 00:00:01 |


---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---

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

22:41:03 SQL>
22:41:03 SQL> select ename, dname from emp, dept
22:41:03   2   where emp.deptno(+) = dept.deptno and emp.deptno is not null;

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

Execution Plan

---

Plan hash value: 2285423260

---

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

---

|   0 | SELECT STATEMENT   |      |     4 |   160 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     4 |   160 |     5  (20)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMP  |     4 |    80 |     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")    2 - filter("EMP"."DEPTNO" IS NOT NULL)

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

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

Received on Fri Jan 25 2013 - 07:47:52 CET