Re: outer join between parent-child, not null and FK constraints
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-lReceived on Thu Jan 24 2013 - 23:21:49 CET