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

  • From: Iggy Fernandez <iggy_fernandez@xxxxxxxxxxx>
  • To: <ax.mount@xxxxxxxxx>, Stephane Faroult <sfaroult@xxxxxxxxxxxx>
  • Date: Sat, 26 Jan 2013 15:10:03 -0800

> 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 
athttp://goo.gl/HNiRm. Of course, it also requires that only one of the 
twotables be referenced in the select clause. Here is a demonstration.
Kindest regards,
Iggy
[oracle@localhost ~]$ sqlplus hr/hr @join_elimination.sql
SQL*Plus: Release 11.2.0.2.0 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 11.2.0.2.0 - 
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 
11.2.0.2.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real 
Application Testing options
                                          
--
//www.freelists.org/webpage/oracle-l

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 01-2013