| 조인 제거(Join Elimination) |
|---|
| {tip} 1:M 조인시 조인외에 1쪽 테이블을 참조하지 않는다면? 조인 제거!{tip} |
| {code:sql} -- 준비 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 있다)||
|{code:sql}
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;
|
| {code:sql} | 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 | |||||
| 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
|{code:sql}
------------------------------------------------------------------------------------
| 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) | |||||||
|---|---|---|---|---|---|---|---|---|
| {code:sql} 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; {code} | {code:sql} 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; {code} | |||||||
| {code:sql} | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
| 0 | SELECT STATEMENT | 1 | 14 | 00:00:00.01 | 4 | ||
| 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)
|{code:sql}
----------------------------------------------------------------------------------------
| 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) |
|---|---|
| {code:sql} 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;
|{code:sql}
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;
|
| {code:sql} | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
| 0 | SELECT STATEMENT | 1 | 14 | 00:00:00.01 | 4 | ||
| 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)
|{code:sql}
----------------------------------------------------------------------------------------
| 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) | ||||||||
|---|---|---|---|---|---|---|---|---|
| {code:sql} select /*+ gather_plan_statistics */ * from emp e where e.deptno in (select deptno from dept); {code} | ||||||||
| {code:sql} | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
| 0 | SELECT STATEMENT | 1 | 14 | 00:00:00.01 | 4 | ||
| 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)||
|{code:sql}
select /*+ gather_plan_statistics */ * from emp e
where exists (select 1 from dept where deptno = e.deptno);
|
| {code:sql} | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
| 0 | SELECT STATEMENT | 1 | 14 | 00:00:00.01 | 4 | ||
| 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)||
|{code:sql}
select /*+ gather_plan_statistics */ e.empno, e.ename, e.deptno, e.sal, e.hiredate
from emp e, dept d
where e.deptno = d.deptno(+);
|
| {code:sql} | 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 없음 ♡
|
|{code:sql}
-- 조인 제거(Join Elimination)의 제약사항
-- 1. JOIN 되는 FK 가 싱글 컬럼만 된다
-- 2. JOIN 조건은 Equal(=)만 된다
-- 3. SELECT 절에 제거 대상 테이블의 컬럼이 출현하면 안된다 (당근)
-- 4. 10g 이하에서는 ANSI SQL JOIN 문은 안된다. (11g 부터 가능)
|
이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.
이 문서의 내용은 (주)오픈메이드컨설팅에서 출간한 'THE LOGICAL OPTIMIZER'를 참고하였습니다.