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

From: Iggy Fernandez <>
Date: Sat, 26 Jan 2013 15:10:03 -0800
Message-ID: <BLU165-W125D036B89E10765A93483EB1A0_at_phx.gbl>

> Hi Stephane> I am asking this simply because I was thinking if Oracle is capable to> eliminate join from 11gR1 why it cannot eliminate redundant outer joins.> That's all. I am not meaning we should not use outer join nor write proper> queries. I simply wanted to prove that my thinking about outerjoin+fk+not> null, if all 3 conditions are met outer join is not needed
Hi, Alex,
I was initially confused because you used the term "join elimination" in asense that is different from the sense in which it is conventionally used.The Oracle optimizer can perform join elimination for both inner and outerjoins. When this happens, only one table is accessed during query executionnot two. However, in the case you outlined, this kind of join eliminationcannot be performed because you are selecting columns from both tables. What you really want Oracle to do is not eliminate one table from the joinbut use the inner join code path instead of the outer join code path sinceboth code paths will produce the same results in your case. However sucha substitution of one code path for another is not necessary since thereis no performance advantage to be be gained from doing so. Inner join,outer join, semi-join (existence), and anti-join (non-existence) allrequire exactly the same amount of work. Once again, you are not asking for "elimination" but "substitution."Elimination is not possible in the case you outlined while substitutiondoes not create any advantage. The belief that outer join is more complex or more expensive that innerjoin may have originated in the time when outer join in other databases(not Oracle) required a union as follows: select emp.first_name || ' ' || emp.last_name as employee_name, dep.department_namefrom employees emp, departments depwhere emp.department_id = dep.department_idunionselect emp.first_name || ' ' || emp.last_name as employee_name, null as department_namefrom employees empwhere not exists( select * from departments dep where dep.department_id = emp.department_id)order by 1; Finally, I would like to point out that, in the outer join case that yououtlined, "join elimination" (in the conventional sense not in the sensethat you meant in your question) requires neither the foreign key constraintEMP_DEPT_FK, nor the not-null constraint on the EMPLOYEES.DEPARTMENT_IDcolumn, nor even the primary key constraint on the DEPARTMENTS table. Itonly requires a uniqueness constraint or unique index on the columnDEPARTMENTS.DEPARTMENT_ID. This is explained in Maria Colgan's post at Of course, it also requires that only one of the twotables be referenced in the select clause. Here is a demonstration. Kindest regards,
[oracle_at_localhost ~]$ sqlplus hr/hr @join_elimination.sql SQL*Plus: Release Production on Sat Jan 26 14:54:42 2013 Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:Oracle Database 11g Enterprise Edition Release - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set pagesize 1000SQL> SQL> describe employees; Name                                      Null?    Type ----------------------------------------- -------- ---------------------------- EMPLOYEE_ID                               NOT NULL NUMBER(6) FIRST_NAME                                         VARCHAR2(20) LAST_NAME                                 NOT NULL VARCHAR2(25) EMAIL                                     NOT NULL VARCHAR2(25) PHONE_NUMBER                                       VARCHAR2(20) HIRE_DATE                                 NOT NULL DATE JOB_ID                                    NOT NULL VARCHAR2(10) SALARY                                             NUMBER(8,2) COMMISSION_PCT                                     NUMBER(2,2) MANAGER_ID                                         NUMBER(6) DEPARTMENT_ID                                      NUMBER(4)
SQL> describe departments; Name                                      Null?    Type ----------------------------------------- -------- ---------------------------- DEPARTMENT_ID                             NOT NULL NUMBER(4) DEPARTMENT_NAME                           NOT NULL VARCHAR2(30) MANAGER_ID                                         NUMBER(6) LOCATION_ID                                        NUMBER(4)
SQL> describe job_history; Name                                      Null?    Type ----------------------------------------- -------- ---------------------------- EMPLOYEE_ID                               NOT NULL NUMBER(6) START_DATE                                NOT NULL DATE END_DATE                                  NOT NULL DATE JOB_ID                                    NOT NULL VARCHAR2(10) DEPARTMENT_ID                                      NUMBER(4)
SQL> SQL> break on table_name skip 1SQL> SQL> select table_name, constraint_type, constraint_name from user_constraints  2  where (table_name, constraint_type) in (  3    ('EMPLOYEES', 'R'),  4    ('EMPLOYEES', 'C'),  5    ('DEPARTMENTS', 'P'),  6    ('JOB_HISTORY', 'R')  7  )  8  order by 1, 2;
TABLE_NAME                     C CONSTRAINT_NAME------------------------------ - ------------------------------DEPARTMENTS                    P DEPT_ID_PK
EMPLOYEES                      C EMP_EMAIL_NN                               C EMP_HIRE_DATE_NN                               C EMP_JOB_NN                               C EMP_LAST_NAME_NN                               C EMP_SALARY_MIN                               R EMP_DEPT_FK                               R EMP_JOB_FK                               R EMP_MANAGER_FK
JOB_HISTORY                    R JHIST_JOB_FK                               R JHIST_EMP_FK                               R JHIST_DEPT_FK

12 rows selected.
SQL> SQL> select index_name, uniqueness from user_indexes 2 where table_name = 'DEPARTMENTS';

INDEX_NAME                     UNIQUENES------------------------------ ---------DEPT_LOCATION_IX               NONUNIQUEDEPT_ID_PK                     UNIQUE
SQL> SQL> alter table employees drop constraint emp_dept_fk; Table altered.
SQL> alter table job_history drop constraint jhist_dept_fk; Table altered.
SQL> alter table departments drop constraint dept_id_pk; Table altered.
SQL> SQL> set linesize 250SQL> column employee_name format a30SQL> alter session set "_rowsource_execution_statistics"=true; Session altered.
SQL> SQL> -- join two tables but select columns from only one tableSQL> select emp.first_name || ' ' || emp.last_name as employee_name 2 from employees emp, departments dep 3 where emp.department_id = dep.department_id(+) 4 order by 1;
EMPLOYEE_NAME------------------------------Adam FrippAlana WalshAlberto ErrazurizAlexander HunoldAlexander KhooAlexis BullAllan McEwenAlyssa HuttonAmit BandaAnthony Cabrio[chomp]William GietzWilliam SmithWinston Taylor
107 rows selected.
SQL> SQL> select * from table(dbms_xplan.display_cursor(null, null, 'typical iostats -bytes -cost'));
PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID  2tux68u1dk42k, child number 0-------------------------------------select emp.first_name || ' ' || emp.last_name as employee_name fromemployees emp, departments dep where emp.department_id Ūp.department_id(+) order by 1
Plan hash value: 1502791934
--------------------------------------------------------------------------------------------------------------| Id  | Operation        | Name        | Starts | E-Rows | E-Time   | A-Rows |   A-Time   | Buffers | Reads  |--------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT |             |      1 |        |          |    107 |00:00:00.01 |       1 |      1 ||   1 |  SORT ORDER BY   |             |      1 |    107 | 00:00:01 |    107 |00:00:00.01 |       1 |      1 ||   2 |   INDEX FULL SCAN| EMP_NAME_IX |      1 |    107 | 00:00:01 |    107 |00:00:00.01 |       1 |      1 |--------------------------------------------------------------------------------------------------------------
Note----- - dynamic sampling used for this statement (level=2)

20 rows selected.
SQL> SQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options                                                

Received on Sun Jan 27 2013 - 00:10:03 CET

Original text of this message