by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

6. 조인 제거




조인 제거(Join Elimination)
1:M 조인시 조인외에 1쪽 테이블을 참조하지 않는다면? 조인 제거!

-- 준비
drop table emp;
drop table dept;

create table emp as select * from scott.emp;
create table dept as select * from scott.dept;
조인 제거 데모 (PK, FK 없다) 조인 제거 데모 (PK, FK 있다)
select /*+ gather_plan_statistics */ e.empno, e.ename, e.deptno, e.sal, e.hiredate
  from dept d, emp e
 where d.deptno = e.deptno;
alter table emp add constraint emp_pk primary key (empno);
alter table dept add constraint dept_pk primary key (deptno);
-- FK 를 만들었는데 오히려 성능이 향상되는 경우
alter table emp add constraint emp_fk_dept foreign key (deptno) references dept (deptno);

exec dbms_stats.gather_table_stats (ownname => 'uadmin', tabname => 'emp' , degree => 1, cascade => TRUE);
exec dbms_stats.gather_table_stats (ownname => 'uadmin', tabname => 'dept' , degree => 1, cascade => TRUE);

select /*+ gather_plan_statistics */ e.empno, e.ename, e.deptno, e.sal, e.hiredate
  from dept d, emp e
 where d.deptno = e.deptno;

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |     14 |00:00:00.01 |       7 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |     14 |     14 |00:00:00.01 |       7 |  1517K|  1517K|  637K (0)|
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |      4 |00:00:00.01 |       3 |       |       |          |
|   3 |   TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       4 |       |       |          |
----------------------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      OPT_PARAM('optimizer_index_cost_adj' 25)
      OPT_PARAM('optimizer_index_caching' 90)
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "D"@"SEL$1")
      FULL(@"SEL$1" "E"@"SEL$1")
      LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
      USE_HASH(@"SEL$1" "E"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("D"."DEPTNO"="E"."DEPTNO")

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

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |     14 |00:00:00.01 |       4 |
|*  1 |  TABLE ACCESS FULL| EMP  |      1 |     10 |     14 |00:00:00.01 |       4 |
------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      OPT_PARAM('optimizer_index_cost_adj' 25)
      OPT_PARAM('optimizer_index_caching' 90)
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"SEL$F7859CDE")
      ELIMINATE_JOIN(@"SEL$1" "D"@"SEL$1")
      OUTLINE(@"SEL$1")
      FULL(@"SEL$F7859CDE" "E"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("E"."DEPTNO" IS NOT NULL)
조인 제거 데모 (eliminate_join) 조인 제거 데모 (no_eliminate_join)
select /*+ gather_plan_statistics eliminate_join(d) */ e.empno, e.ename, e.deptno, e.sal, e.hiredate
  from dept d, emp e
 where d.deptno = e.deptno;
select /*+ gather_plan_statistics no_eliminate_join(d) */ e.empno, e.ename, e.deptno, e.sal, e.hiredate
  from dept d, emp e
 where d.deptno = e.deptno;

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |     14 |00:00:00.01 |       4 |
|*  1 |  TABLE ACCESS FULL| EMP  |      1 |     10 |     14 |00:00:00.01 |       4 |
------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      OPT_PARAM('optimizer_index_cost_adj' 25)
      OPT_PARAM('optimizer_index_caching' 90)
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"SEL$F7859CDE")
      ELIMINATE_JOIN(@"SEL$1" "D"@"SEL$1")
      OUTLINE(@"SEL$1")
      FULL(@"SEL$F7859CDE" "E"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("E"."DEPTNO" IS NOT NULL)

----------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |      1 |        |     14 |00:00:00.01 |       8 |
|   1 |  NESTED LOOPS      |         |      1 |     10 |     14 |00:00:00.01 |       8 |
|   2 |   TABLE ACCESS FULL| EMP     |      1 |     10 |     14 |00:00:00.01 |       4 |
|*  3 |   INDEX UNIQUE SCAN| DEPT_PK |     14 |      1 |     14 |00:00:00.01 |       4 |
----------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      OPT_PARAM('optimizer_index_cost_adj' 25)
      OPT_PARAM('optimizer_index_caching' 90)
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "E"@"SEL$1")
      INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))
      LEADING(@"SEL$1" "E"@"SEL$1" "D"@"SEL$1")
      USE_NL(@"SEL$1" "D"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("D"."DEPTNO"="E"."DEPTNO")
조인 제거 데모 (_optimizer_join_elimination_enabled = true) 조인 제거 데모 (_optimizer_join_elimination_enabled = false)
alter session set "_optimizer_join_elimination_enabled" = true; 

select /*+ gather_plan_statistics */ e.empno, e.ename, e.deptno, e.sal, e.hiredate
  from dept d, emp e
 where d.deptno = e.deptno;
alter session set "_optimizer_join_elimination_enabled" = false; 

select /*+ gather_plan_statistics */ e.empno, e.ename, e.deptno, e.sal, e.hiredate
  from dept d, emp e
 where d.deptno = e.deptno;

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |     14 |00:00:00.01 |       4 |
|*  1 |  TABLE ACCESS FULL| EMP  |      1 |     10 |     14 |00:00:00.01 |       4 |
------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      OPT_PARAM('optimizer_index_cost_adj' 25)
      OPT_PARAM('optimizer_index_caching' 90)
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"SEL$F7859CDE")
      ELIMINATE_JOIN(@"SEL$1" "D"@"SEL$1")
      OUTLINE(@"SEL$1")
      FULL(@"SEL$F7859CDE" "E"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("E"."DEPTNO" IS NOT NULL)

----------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |      1 |        |     14 |00:00:00.01 |       8 |
|   1 |  NESTED LOOPS      |         |      1 |     10 |     14 |00:00:00.01 |       8 |
|   2 |   TABLE ACCESS FULL| EMP     |      1 |     10 |     14 |00:00:00.01 |       4 |
|*  3 |   INDEX UNIQUE SCAN| DEPT_PK |     14 |      1 |     14 |00:00:00.01 |       4 |
----------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      OPT_PARAM('_optimizer_join_elimination_enabled' 'false')
      OPT_PARAM('optimizer_index_cost_adj' 25)
      OPT_PARAM('optimizer_index_caching' 90)
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "E"@"SEL$1")
      INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))
      LEADING(@"SEL$1" "E"@"SEL$1" "D"@"SEL$1")
      USE_NL(@"SEL$1" "D"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("D"."DEPTNO"="E"."DEPTNO")
조인 제거 데모 (IN)
select /*+ gather_plan_statistics */ * from emp e
 where e.deptno in (select deptno from dept);

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |     14 |00:00:00.01 |       4 |
|*  1 |  TABLE ACCESS FULL| EMP  |      1 |     10 |     14 |00:00:00.01 |       4 |
------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      OPT_PARAM('optimizer_index_cost_adj' 25)
      OPT_PARAM('optimizer_index_caching' 90)
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"SEL$D53E76E4")
      ELIMINATE_JOIN(@"SEL$5DA710D3" "DEPT"@"SEL$2")
      OUTLINE(@"SEL$5DA710D3")
      UNNEST(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      FULL(@"SEL$D53E76E4" "E"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("E"."DEPTNO" IS NOT NULL)
조인 제거 데모 (EXIST)
select /*+ gather_plan_statistics */ * from emp e
 where exists (select 1 from dept where deptno = e.deptno);

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |     14 |00:00:00.01 |       4 |
|*  1 |  TABLE ACCESS FULL| EMP  |      1 |     10 |     14 |00:00:00.01 |       4 |
------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      OPT_PARAM('optimizer_index_cost_adj' 25)
      OPT_PARAM('optimizer_index_caching' 90)
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"SEL$D53E76E4")
      ELIMINATE_JOIN(@"SEL$5DA710D3" "DEPT"@"SEL$2")
      OUTLINE(@"SEL$5DA710D3")
      UNNEST(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      FULL(@"SEL$D53E76E4" "E"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("E"."DEPTNO" IS NOT NULL)
조인 제거 데모 (OUTER JOIN)
select /*+ gather_plan_statistics */ e.empno, e.ename, e.deptno, e.sal, e.hiredate
  from emp e, dept d
 where e.deptno = d.deptno(+);

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |     14 |00:00:00.01 |       4 |
|   1 |  TABLE ACCESS FULL| EMP  |      1 |     10 |     14 |00:00:00.01 |       4 |
------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      OPT_PARAM('optimizer_index_cost_adj' 25)
      OPT_PARAM('optimizer_index_caching' 90)
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"SEL$F7859CDE")
      ELIMINATE_JOIN(@"SEL$1" "D"@"SEL$1")
      OUTLINE(@"SEL$1")
      FULL(@"SEL$F7859CDE" "E"@"SEL$1")
      END_OUTLINE_DATA
  */
-- Predicate Information 없음 ♡

-- 조인 제거(Join Elimination)의 제약사항
--  1. JOIN 되는 FK 가 싱글 컬럼만 된다
--  2. JOIN 조건은 Equal(=)만 된다
--  3. SELECT 절에 제거 대상 테이블의 컬럼이 출현하면 안된다 (당근)
--  4. 10g 이하에서는 ANSI SQL JOIN 문은 안된다. (11g 부터 가능)


이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.
이 문서의 내용은 (주)오픈메이드컨설팅에서 출간한 'THE LOGICAL OPTIMIZER'를 참고하였습니다.

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.