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

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



| 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
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jan 24 2013 - 23:21:49 CET

Original text of this message