Optimizer Mode를
변경하는 힌트 (RULE)
구로디지털 오엔제이프로그래밍실무교육센터
www.onjprogramming.co.kr
규칙
기반 옵티마이저(Rule-Based Optimizer)로 동작하여 실행 계획을 세우도록 하는 힌트인데
이 경우 테이블이나 인덱스의 통계 정보가 있다고 하더라도 무시하고 사용하지 않으며 규칙에 기반한 실행 계획을 세우게 됩니다.
옵티마이저는 순위가 매겨진 오퍼레이션에 근거하여 실행 계획을 세우며 순위가 높은 것이 우선 적용 됩니다. (어떤 규칙이 있는지 이전 강좌 “규칙 기반 옵티마이저”를 참고 하세요)
만약 SQL 문장에서 과 같이 RULE 힌트와 다른 힌트가 같이 사용된다면 RULE 힌트만
적용되니 이점 주의!!
select
e.empno,
e.ename,
d.dname
from scott.emp e, scott.dept d
where e.deptno = d.deptno
Execution Plan
---------------------------------------------------------------
Operation
Object Name Rows Bytes
Cost
SELECT STATEMENT
Optimizer Mode=HINT: RULE
NESTED LOOPS
TABLE ACCESS FULL SCOTT.EMP
TABLE ACCESS BY
INDEX ROWID SCOTT.DEPT
INDEX UNIQUE
SCAN SCOTT.PK_DEPT
이번엔 Rule Based 이면서 인덱스를 사용하지 못하도록 to_char를 붙였습니다. 다들 아시죠? 인덱스 컬럼에 변형을 가하면 인덱스를 사용하지 못한다는 사실을!
그래서 FULL SCAN해서 정렬 후 MERGE
JOIN을 하는 실행 계획으로 바뀌었습니다. 그리 좋은 방법은 되지 못하는 것
같습니다. 그리고 RBO를 이용하는 경우엔 철저히 원칙을
지켜야 합니다.
select
e.empno,
e.ename,
d.dname
from scott.emp e, scott.dept d
where to_char(e.deptno) = to_char(d.deptno)
Execution Plan
-------------------------------------------------------------------
Operation
Object Name Rows Bytes
Cost
SELECT STATEMENT Optimizer Mode=HINT: RULE
MERGE JOIN
SORT JOIN
TABLE ACCESS
FULL SCOTT.DEPT
SORT JOIN
TABLE ACCESS
FULL SCOTT.EMP
이번엔
똑 같은 Query이지만 ALL_ROWS 힌트로 이용해
봅니다. 현재 EMP, DEPT 테이블의 통계 정보는
생성되어 있습니다. 11g에서는 EMP, DEPT의 데이터
건수가 얼마 안되니 FULL SCAN해서 HASH
JOIN을 하는 방법으로 실행계획을 만들었습니다.
select
e.empno,
e.ename,
d.dname
from scott.emp e, scott.dept d
where to_char(e.deptno) = to_char(d.deptno)
Execution Plan
-----------------------------------------------------------------
Operation
Object Name Rows Bytes
Cost
SELECT STATEMENT Optimizer Mode=HINT: ALL_ROWS 14
7
HASH JOIN 14
294 7
TABLE ACCESS
FULL SCOTT.DEPT 4
44
3
TABLE ACCESS
FULL SCOTT.EMP 14
140
3
규칙
기반 옵티마이저에서 조인시 적용되는 원칙은 다음과 같습니다.
조인되는
두 컬럼 모두에 인덱스가 있는 경우 FROM절에서 나중에 나타나는 테이블이 드라이빙 테이블이 되어
먼저 SCAN하고 그 건건이 LOOP를 돌면서 비드라이빙
테이블을 인덱스 UYNIQUE SCAN을 하게 될겁니다.
비용 기반 옵티마이저에서는 FROM절에 나타나는 테이블의 순서와 관계없이 통계 정보에
따라 드라이빙 테이블이 결정 됩니다. 물론 ORDERED
힌트를 사용하는 경우는 예외인데 이 경우 FROM 절에 나오는 테이블의 순서대로
테이블들이 조인 됩니다.
[조인되는 두 테이블 모두에 인덱스가 있는 경우]
create index idx_emp_deptno on emp(deptno)
select
e.empno,
e.ename,
d.dname
from emp e, dept d
where e.deptno = d.deptno;
Execution Plan
-------------------------------------------------------------------
Operation
Object Name Rows Bytes
Cost
SELECT STATEMENT Optimizer Mode=HINT: RULE
TABLE ACCESS BY
INDEX ROWID SCOTT.EMP
NESTED
LOOPS
TABLE ACCESS FULL SCOTT.DEPT
ß 드라이빙
테이블
:namespace
prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" />INDEX RANGE
select
e.empno,
e.ename,
d.dname
from dept d, emp e
where e.deptno = d.deptno;
Execution Plan
-------------------------------------------------------------------
Operation
Object Name Rows Bytes
Cost
SELECT STATEMENT Optimizer Mode=HINT: RULE
NESTED
LOOPS
TABLE ACCESS FULL SCOTT.EMP
ß 드라이빙
테이블
TABLE ACCESS
BY INDEX ROWID SCOTT.DEPT
INDEX
UNIQUE SCAN SCOTT.PK_DEPT
조인 컬럼중 한 테이블에만
인덱스가 있는 경우 FROM절 이후 테이블의 순서 보다 인덱스가 생성되어 있지 않은 테이블이 드라이빙
테이블이 되는 실행 계획을 세우게 됩니다. 아래에서
DEPT 테이블의 DEPTNO는 PK로서
인덱스가 있지만 EMP 테이블의 DEPTNO는 인덱스가
없는 경우 입니다.
아래에서는 EMP 테이블이 드라이빙 테이블 입니다.
[조인되는 두 테이블중 한 테이블에만 인덱스가 있는 경우]
select
e.empno,
e.ename,
d.dname
from emp e, dept d
where e.deptno = d.deptno;
Execution Plan
------------------------------------------------------------------
Operation
Object Name Rows Bytes
Cost
SELECT STATEMENT Optimizer Mode=HINT: RULE
NESTED LOOPS
TABLE ACCESS
FULL SCOTT.EMP
TABLE ACCESS BY
INDEX ROWID SCOTT.DEPT
INDEX UNIQUE
SCAN SCOTT.PK_DEPT
select
e.empno,
e.ename,
d.dname
from dept d, emp e
where e.deptno = d.deptno;
Operation
Object Name Rows Bytes
Cost
SELECT STATEMENT Optimizer Mode=HINT: RULE
NESTED LOOPS
TABLE ACCESS
FULL SCOTT.EMP
TABLE ACCESS BY
INDEX ROWID SCOTT.DEPT
INDEX UNIQUE
SCAN SCOTT.PK_DEPT
[실습] 아래 예문에서 만든 테이블로 위의
예문을 실습해 보라.
-- emptest 테이블은
250만건
CREATE TABLE
emptest
AS
SELECT
ROWNUM AS
empno
, MOD(ROWNUM,5) AS deptno--5개씩 그룹핑
(0,1,2,3,4값만가짐)
, '홍길동'||ROWNUM AS
ename --랜덤 문자5개
, '서울'||ROWNUM AS addr
, DBMS_RANDOM.STRING('u',5) AS random --랜덤문자
5개
FROM dual
CONNECT
BY ROWNUM <= 2500000 --250만건
만들자...
-- depttest
테이블은 5건
CREATE TABLE
depttest
AS
SELECT
MOD(ROWNUM,5) AS
deptno--5개씩 그룹핑 (0,1,2,3,4값만가짐)
, '부서명'||MOD(ROWNUM,5)
AS dname --랜덤 문자5개
FROM dual
CONNECT
BY ROWNUM <= 5 --5건 만들자...
-- empno
로 primary key를 만들자
alter table emptest add primary key (empno)
--
emptest의 deptno로 인덱스를 만들자.
create index
idx_emptest_deptno on emptest(deptno);
-- 통계정보 생성
exec
DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMPTEST')
exec
DBMS_STATS.GATHER_TABLE_STATS(USER, 'DEPT')
SQL> alter session set optimizer_mode = rule;
세션이
변경되었습니다.
경
과: 00:00:00.01
//depttest 테이블이 드라이빙 테이블이 된다.
deptno로 인덱스가 없으니
SQL> select count(*) from emptest e, depttest d
2 where e.deptno =
d.deptno;
COUNT(*)
----------
2500000
경
과: 00:00:00.40
Execution Plan
----------------------------------------------------------
Plan hash value: 3957333709
--------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS FULL| DEPTTEST |
|* 4 | INDEX RANGE SCAN | IDX_EMPTEST_DEPTNO
|
--------------------------------------------------
//인덱스 컬럼에 변형을 가해서 인덱스를 사용 못하도록
함
SQL> select count(*) from
emptest e, depttest d
2 where to_char(e.deptno) =
to_char(d.deptno);
COUNT(*)
----------
2500000
경
과: 00:00:01.36
-----------------------------------------
| Id | Operation | Name |
-----------------------------------------
| 0 | SELECT STATEMENT |
|
| 1 | SORT AGGREGATE | |
| 2 | MERGE JOIN | |
| 3 | SORT JOIN | |
| 4 | TABLE ACCESS FULL| DEPTTEST
|
|* 5 | SORT JOIN | |
| 6 | TABLE ACCESS FULL| EMPTEST |
-----------------------------------------
//마찬 가지로 depttest 테이블에
인덱스가 없으니 depttest를 드라이빙 테이블로
SQL> select count(*) from
depttest d, emptest e
2 where e.deptno =
d.deptno;
COUNT(*)
----------
2500000
경
과: 00:00:00.14
Execution Plan
----------------------------------------------------------
Plan hash value: 3957333709
--------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS FULL| DEPTTEST |
|* 4 | INDEX RANGE SCAN | IDX_EMPTEST_DEPTNO
|
--------------------------------------------------
댓글 없음:
댓글 쓰기