RE: outer join between parent-child, not null and FK constraints
From: Iggy Fernandez <iggy_fernandez_at_hotmail.com>
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 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_at_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.
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;
SQL> SQL> select * from table(dbms_xplan.display_cursor(null, null, 'typical iostats -bytes -cost'));
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 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_at_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 UNIQUESQL> 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 Taylor107 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
-- http://www.freelists.org/webpage/oracle-lReceived on Sun Jan 27 2013 - 00:10:03 CET