RE: outer join between parent-child, not null and FK constraints
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:
select
emp.first_name || ' ' || emp.last_name as employee_name,
dep.department_name
from employees emp, departments dep
where emp.department_id = dep.department_id
union
select
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 http://goo.gl/HNiRm. Of course, it also requires that only one of the two tables 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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set pagesize 1000 SQL> 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 1 SQL> SQL> select table_name, constraint_type, constraint_name from user_constraints7 )
2 where (table_name, constraint_type) in (
3 ('EMPLOYEES', 'R'),
4 ('EMPLOYEES', 'C'),
5 ('DEPARTMENTS', 'P'),
6 ('JOB_HISTORY', 'R')
8 order by 1, 2;
TABLE_NAME C CONSTRAINT_NAME
------------------------------ - ------------------------------DEPARTMENTS P DEPT_ID_PK
EMPLOYEES C EMP_EMAIL_NNR EMP_JOB_FK
C EMP_HIRE_DATE_NN
C EMP_JOB_NN
C EMP_LAST_NAME_NN
C EMP_SALARY_MIN
R EMP_DEPT_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 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> SQL> set linesize 250 SQL> column employee_name format a30 SQL> alter session set "_rowsource_execution_statistics"=true;
Session altered.
SQL> SQL> -- join two tables but select columns from only one table SQL> select emp.first_name || ' ' || emp.last_name as employee_name3 where emp.department_id = dep.department_id(+)
2 from employees emp, departments dep
4 order by 1;
EMPLOYEE_NAME
Adam Fripp
Alana Walsh
Alberto Errazuriz
Alexander Hunold
Alexander Khoo
Alexis Bull
Allan McEwen
Alyssa Hutton
Amit Banda
Anthony Cabrio
[chomp]
William Gietz
William Smith
Winston 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 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 | --------------------------------------------------------------------------------------------------------------
Note
- dynamic sampling used for this statement (level=2)
20 rows selected.
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
--
http://www.freelists.org/webpage/oracle-l
Received on Sun Jan 27 2013 - 00:28:08 CET