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

From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Thu, 24 Jan 2013 14:21:49 -0800 (PST)
Message-ID: <1359066109.50701.YahooMailNeo_at_web121601.mail.ne1.yahoo.com>

Those table may provide a parent-child relationship but, by default, no primary key/foreign key relationship exists between EMP and DEPT.� You have to create that yourself:
�

SQL> set autotrace on
SQL>
SQL> select ename, dname

� 2� from emp, dept
� 3� where emp.deptno (+) = dept.deptno;
�

ENAME�� DNAME
---------- --------------

SMITH����� RESEARCH
ALLEN����� SALES
WARD������ SALES
JONES����� RESEARCH
MARTIN���� SALES
BLAKE����� SALES
CLARK����� ACCOUNTING
SCOTT����� RESEARCH
KING������ ACCOUNTING
TURNER���� SALES

ADAMS�� RESEARCH
ENAME�� DNAME
---------- --------------

JAMES����� SALES
FORD������ RESEARCH
MILLER���� ACCOUNTING

�� OPERATIONS
�

15 rows selected.
Execution Plan

Plan hash value: 3713469723

|�� 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| DEPT |���� 4 |��� 88 |���� 3�� (0)| 00:00:01 |
|�� 3 |�� TABLE ACCESS FULL| EMP� |��� 14 |�� 280 |���� 3�� (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):

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

�� - dynamic sampling used for this statement (level=2)
Statistics

�� 9� recursive calls
�� 0� db block gets
�� 31� consistent gets
�� 0� physical reads
�� 0� redo size
�� 773� bytes sent via SQL*Net to client
�� 419� bytes received via SQL*Net from client
�� 2� SQL*Net roundtrips to/from client
�� 0� sorts (memory)
�� 0� sorts (disk)
�� 15� rows processed
�

SQL>
SQL> set autotrace off
SQL>
SQL> select * from user_constraints;

�

No rows selected.

SQL>
SQL> alter table dept add constraint dept_pk primary key(deptno);
�

Table altered.
�

SQL>
SQL> alter table emp add constraint emp_pk primary key(empno);
�

Table altered.
�

SQL>
SQL> alter table emp add constraint emp_dept_fk foreign key(deptno) references dept;
�

Table altered.
�

SQL>
SQL> set autotrace on
SQL>
SQL> select ename, dname

� 2� from emp, dept
� 3� where emp.deptno (+) = dept.deptno;
�

ENAME�� DNAME
---------- --------------

CLARK����� ACCOUNTING
KING������ ACCOUNTING
MILLER���� ACCOUNTING
JONES����� RESEARCH
FORD������ RESEARCH
ADAMS����� RESEARCH
SMITH����� RESEARCH
SCOTT����� RESEARCH
WARD������ SALES
TURNER���� SALES
ALLEN����� SALES
ENAME����� DNAME

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

JAMES����� SALES
BLAKE����� SALES
MARTIN���� SALES

�� OPERATIONS
�

15 rows selected.

Execution Plan

Plan hash value: 439339440

| Id� | Operation������������������� | Name��� | Rows� | Bytes | Cost (%CPU)| Time���� |
----------------------------------------------------------------------------------------
|�� 0 | SELECT STATEMENT������������ |�������� |��� 14 |�� 588 |���� 5� (20)| 00:00:01 |
|�� 1 |� MERGE JOIN OUTER����������� |�������� |��� 14 |�� 588 |���� 5� (20)| 00:00:01 |
|�� 2 |�� TABLE ACCESS BY INDEX ROWID| DEPT��� |���� 4 |��� 88 |���� 1�� (0)| 00:00:01 |
|�� 3 |��� INDEX FULL SCAN���������� | DEPT_PK |���� 4 |������ |���� 1�� (0)| 00:00:01 |
|*� 4 |�� SORT JOIN����������������� |�������� |��� 14 |�� 280 |���� 4� (25)| 00:00:01 |

|�� 5 |�� TABLE ACCESS FULL�� | EMP�� |�� 14 |�� 280 |�� 3�� (0)| 00:00:01 |

Predicate Information (identified by operation id):

�� 4 - access("EMP"."DEPTNO"(+)="DEPT"."DEPTNO")
�� filter("EMP"."DEPTNO"(+)="DEPT"."DEPTNO")
Note

�� - dynamic sampling used for this statement (level=2)

Statistics

�� 36� recursive calls
�� 0� db block gets
�� 71� consistent gets
�� 0� physical reads
�� 0� redo size
�� 708� bytes sent via SQL*Net to client
�� 419� bytes received via SQL*Net from client
�� 2� SQL*Net roundtrips to/from client
�� 7� sorts (memory)
�� 0� sorts (disk)
�� 15� rows processed
�

SQL>
SQL> set autotrace off
SQL>

Yes, Oracle is smart enough to not perform the outer� join when the constraints actually exist.
�

David Fitzjarrell

From: amonte <ax.mount_at_gmail.com>
To: Oracle-L Group <oracle-l_at_freelists.org> Sent: Thursday, January 24, 2013 2:06 PM 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:

|� 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 - 23:21:49 CET

This message: [ Message body ]
Next message: David Fitzjarrell: "Re: Design question regarding event log table processing"
Previous message: Lu Jiang: "Re: Install RAC failed at running root.sh step"
In reply to: amonte: "outer join between parent-child, not null and FK constraints"
Next in thread: amonte: "Re: outer join between parent-child, not null and FK constraints"
Reply: amonte: "Re: outer join between parent-child, not null and FK constraints"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message