NO_EXPAND 힌트는 CBO(COST BASED Optimizer) 모드에서 OR 조건이나 IN List등을 사용할 때 OR확장 (Concatenation등을)을 막는 것인데 실행 계획에 Concatenation등이 보이는 경우 이곳이 일어나지 않도록 처리해 줍니다.
구로디지털 오엔제이프로그래밍실무교육센터
즉 OR를 UNION-ALL로 풀지 말고
아래의 예를 보죠~
[형식]
실습을 위해 먼저 옵티마이저 모드를 RULE로 바꾼 후 …
왜 바꾸냐 하면? EMP 테이블의 경우 데이터 양이 적으므로 OR를 사용하더라도 FULL SCAN하는 실행계획을 만들어 내므로 고의로 CONCATENATION을 만들어 내기 위해 RULE BASED Optimizer Mode로 변경하는 것입니다. 물론 옵티마이저 모드를 CHOOSE로 한 후 테이블의 통계 정보를 삭제하는 경우에도 동일 합니다.
alter session set optimizer_mode=rule;
SELECT ename, sal
FROM EMP
WHERE JOB = 'CLERK'
OR JOB = 'SALESMAN'
-----------------------------------------------------------------
Operation Object Name Rows Bytes Cost
-----------------------------------------------------------------
SELECT STATEMENT Optimizer Mode=RULE
CONCATENATION
TABLE ACCESS BY INDEX ROWID SCOTT.EMP
INDEX <st1:placetype w:st="on">RANGE</st1:placetype> SCAN SCOTT.IDX_EMP_JOB
TABLE ACCESS BY INDEX ROWID SCOTT.EMP
INDEX <st1:placetype w:st="on">RANGE</st1:placetype> SCAN SCOTT.IDX_EMP_JOB
SELECT
ENAME, SAL
FROM EMP
WHERE JOB = 'CLERK'
OR JOB = 'SALESMAN'
Operation Object Name Rows Bytes Cost
SELECT STATEMENT Optimizer Mode=RULE 6 2
INLIST ITERATOR
TABLE ACCESS BY INDEX ROWID SCOTT.EMP 6 96 2
INDEX <st1:placetype w:st="on">RANGE</st1:placetype> SCAN SCOTT.IDX_EMP_JOB 6 1
[실습]
- 실습을 위한 예제 테이블 및 데이터는 아래 링크에서 확인 바랍니다.
myemp1 : 1000만건
myemp1_old : 100만건
mydept : 5건
테스트환경 : oracle 11g
아래와 같은 결과를 내기 위한 힌트 구문을 이해하고 왜 inlist iterator가 빠른지 느껴 보라.
먼저 인덱스를 하나 만들자.
SQL> create index idx_myemp1_sal on myemp1(sal);
인덱스가 생성되었습니다.
n 옵티마이저가 사용 못하도록 일단 숨기고…
SQL> alter index idx_myemp1_sal invisible;
인덱스가 변경되었습니다.
n 인덱스가 없는 경우엔 무조건 FULL SCAN을 한다.
SQL> select count(*) from myemp1
2 where sal = 100000
3 or sal = 200000
4 or sal = 300000;
COUNT(*)
----------
15
경 과: 00:00:08.85
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 17035 (2)| 00:03:25 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| MYEMP1 | 15 | 75 | 17035 (2)| 00:03:25 |
-----------------------------------------------------------------------------
SQL> select count(*) from myemp1
2 where sal = 100000
3 or sal = 200000
4 or sal = 300000;
COUNT(*)
----------
15
경 과: 00:00:08.82
Execution Plan
----------------------------------------------------------
Plan hash value: 3929728256
-------------------------------------
| Id | Operation | Name |
-------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
|* 2 | TABLE ACCESS FULL| MYEMP1 |
n 인덱스를 다시 보이도록 하자.
SQL> alter index idx_myemp1_sal visible;
인덱스가 변경되었습니다.
SQL> select count(*) from myemp1
2 where sal = 100000
3 or sal = 200000
4 or sal = 300000;
COUNT(*)
----------
15
경 과: 00:00:00.01
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 5 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | INLIST ITERATOR | | | | | |
|* 3 | INDEX RANGE SCAN| IDX_MYEMP1_SAL | 15 | 75 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
n 아래처럼 힌트를 안써도 CBO인 경우 알아서 인덱스를 사용한다.
SQL> select count(*) from myemp1
2 where sal = 100000
3 or sal = 200000
4 or sal = 300000;
COUNT(*)
----------
15
경 과: 00:00:00.00
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 5 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | INLIST ITERATOR | | | | | |
|* 3 | INDEX RANGE SCAN| IDX_MYEMP1_SAL | 15 | 75 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
SQL> select count(*) from myemp1
2 where sal = 100000
3 or sal = 200000
4 or sal = 300000;
COUNT(*)
----------
15
경 과: 00:00:00.01
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | CONCATENATION | |
|* 3 | INDEX RANGE SCAN| IDX_MYEMP1_SAL |
|* 4 | INDEX RANGE SCAN| IDX_MYEMP1_SAL |
|* 5 | INDEX RANGE SCAN| IDX_MYEMP1_SAL |
---------------------------------------------
where절에 3개의 or 만으로는 inlist iterrator와 concatenation을 비교해 보기 어려워
이번에는 sal > 900000 이라는조건을 하나 더 줘보자. 그리고 통계정보도 보기 위해
set autotrace on 이라고 하자.
SQL>set autotrace on;
SQL> select count(*) from myemp1
2 where sal = 100000
3 or sal = 200000
4 or sal = 300000
5 or sal > 900000;
COUNT(*)
----------
5500010
경 과: 00:00:02.53
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | CONCATENATION | |
|* 3 | INDEX RANGE SCAN| IDX_MYEMP1_SAL |
|* 4 | INDEX RANGE SCAN| IDX_MYEMP1_SAL |
|* 5 | INDEX RANGE SCAN| IDX_MYEMP1_SAL |
|* 6 | INDEX RANGE SCAN| IDX_MYEMP1_SAL |
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
12973 consistent gets
12963 physical reads
0 redo size
438 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select count(*) from myemp1
2 where sal = 100000
3 or sal = 200000
4 or sal = 300000
5 or sal > 900000;
COUNT(*)
----------
5500010
경 과: 00:00:01.50
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 12745 (1)| 00:02:33 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | CONCATENATION | | | | | |
| 3 | INLIST ITERATOR | | | | | |
|* 4 | INDEX RANGE SCAN| IDX_MYEMP1_SAL | 15 | 75 | 5 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_MYEMP1_SAL | 5499K| 26M| 12740 (1)| 00:02:33 |
--------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
12972 consistent gets
0 physical reads
0 redo size
438 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
4번 index range scan 후 concatenation 했을 때는 physical read가 발생하며
inlist iterator의 경우 physical read가 0이다… 그리고 시간도 조금 단축되고
확인 바란다. 쿼리가 단문인 경우 별차이 없다고 느끼겠지만 장문의 반복적인 쿼리 에서는 많은 성능 차이가 날 것이다. 그리고 CBO인 경우 no_expend를 사용 안하더라도 인덱스가 있는 경우 알아서 inlist iterator 연산을 수행하도록 실행계획을 작성하니 별 걱정 안해도 될 것 같다.
 | |||||
|---|---|---|---|---|---|
[기업100%환급]Spring ,MyBatis,Hibernate실무과정  | 총 | 5일 | 40시간 | 01-27 | |
[기업100%환급]자바기초에서 JDBC, Servlet/JSP까지  | 총 | 5일 | 40시간 | 02-05 | |
 | |||||
| 자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지 | 총 | 24일 | 72시간 | 01-24 | |
| Spring3.X, MyBatis, Hibernate실무과정 | 총 | 12일 | 36시간 | 02-03 | |
 | |||||
| Spring3.X, MyBatis, Hibernate실무과정 | 총 | 5일 | 35시간 | 02-15 | |
| 자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지 | 총 | 10일 | 70시간 | 02-15 | |
| JAVA&WEB프레임워크실무과정 | 총 | 14일 | 98시간 | 03-08 | |
댓글 없음:
댓글 쓰기