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

From: Iggy Fernandez <>
Date: Sat, 26 Jan 2013 15:28:08 -0800
Message-ID: <BLU165-W9FC89EA0DE920D089693EEB1A0_at_phx.gbl>

Trying again using plain-text mode in Hotmail. Apologies for the spam.

> 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 a sense that is different from the sense in which it is conventionally used. The Oracle optimizer can perform join elimination for both inner and outer joins. When this happens, only one table is accessed during query execution not two. However, in the case you outlined, this kind of join elimination cannot be performed because you are selecting columns from both tables.

What you really want Oracle to do is not eliminate one table from the join but use the inner join code path instead of the outer join code path since both code paths will produce the same results in your case. However such a substitution of one code path for another is not necessary since there is no performance advantage to be be gained from doing so. Inner join, outer join, semi-join (existence), and anti-join (non-existence) all require 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 substitution does not create any advantage.

The belief that outer join is more complex or more expensive that inner join may have originated in the time when outer join in other databases (not Oracle) required a union as follows:

  emp.first_name || ' ' || emp.last_name as employee_name,

from employees emp, departments dep
where emp.department_id = dep.department_id union
  emp.first_name || ' ' || emp.last_name as employee_name,
  null as department_name

from employees emp
where 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 you outlined, "join elimination" (in the conventional sense not in the sense that you meant in your question) requires neither the foreign key constraint EMP_DEPT_FK, nor the not-null constraint on the EMPLOYEES.DEPARTMENT_ID column, nor even the primary key constraint on the DEPARTMENTS table. It only requires a uniqueness constraint or unique index on the column DEPARTMENTS.DEPARTMENT_ID. This is explained in Maria Colgan's post at Of course, it also requires that only one of the two tables 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 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set pagesize 1000
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> break on table_name skip 1
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> select index_name, uniqueness from user_indexes
  2  where table_name = 'DEPARTMENTS';

INDEX_NAME                     UNIQUENES

------------------------------ ---------
DEPT_LOCATION_IX               NONUNIQUE DEPT_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> set linesize 250
SQL> column employee_name format a30
SQL> alter session set "_rowsource_execution_statistics"=true;

Session altered.

SQL> -- join two tables but select columns from only one table
SQL> 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;


Adam Fripp
Alana Walsh
Alberto Errazuriz
Alexander Hunold
Alexander Khoo
Alexis Bull
Allan McEwen
Alyssa Hutton
Amit Banda
Anthony Cabrio
William Gietz
William Smith
Winston Taylor

107 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'typical iostats -bytes -cost'));


SQL_ID  2tux68u1dk42k, child number 0

select emp.first_name || ' ' || emp.last_name as employee_name from employees emp, departments dep where emp.department_id = dep.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 |


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

20 rows selected.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

                                               -- Received on Sun Jan 27 2013 - 00:28:08 CET

Original text of this message