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

From: Taral Desai <taral.desai_at_gmail.com>
Date: Fri, 25 Jan 2013 17:42:12 -0600
Message-ID: <CAO4+9HUnb7QtUnzDx_ESjWqgujSg4PdaJ-=fv83tqxvPLFFL6A_at_mail.gmail.com>

Without Any constraints
TARAL.TARALWIN>select emp.ename, dept.dname from emp, dept where emp.deptno=dept.deptno(+)

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

MILLER     ACCOUNTING
KING       ACCOUNTING
CLARK      ACCOUNTING
FORD       RESEARCH
ADAMS      RESEARCH
SCOTT      RESEARCH
JONES      RESEARCH
SMITH      RESEARCH
JAMES      SALES
TURNER     SALES
BLAKE      SALES
MARTIN     SALES
WARD       SALES
ALLEN      SALES

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


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

| OMem | 1Mem | Used-Mem |

---

| 0 | SELECT STATEMENT | | | | 7 (100)|

 |       |       |          |
|*  1 |  HASH JOIN OUTER   |      |     14 |   588 |     7  (15)| 00:00:01


|   921K|   921K|  582K (0)|

|   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")

Adding constraints

TARAL.TARALWIN>alter table dept add constraint d_pk primary key(deptno);

Table altered.

TARAL.TARALWIN>alter table emp add constraint e_pk primary key(empno);

Table altered.

TARAL.TARALWIN>alter table emp add constraint e_fk_d foreign key(deptno) references dept(deptno);

Table altered.

---

| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time
| OMem | 1Mem | Used-Mem |

---

| 0 | SELECT STATEMENT | | | | 7 (100)|

 |       |       |          |
|*  1 |  HASH JOIN OUTER   |      |     14 |   588 |     7  (15)| 00:00:01


|   921K|   921K|  580K (0)|

|   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")

As, you can see it need to visit DEPT to get that dname otherwise there is JE

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

Plan hash value: 3956160932

---

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

---

| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |

---

On Thu, Jan 24, 2013 at 3:06 PM, amonte <ax.mount_at_gmail.com> wrote:

> 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
>
>
>

-- 

Thanks & Regards,
Taral Desai


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

Received on Sat Jan 26 2013 - 00:42:12 CET