| {code:sql} |
SELECT /*+ INDEX(A IDX_TB_ADDR_01) */ --> WHERE 절 조회 패턴에 상관없이 실행계획 고정
a.*
FROM imsi.tb_addr a
WHERE userid = :b1
AND (name LIKE :b2||'%' OR
email LIKE :b3||'%')
|| INDEX_NAME || COLUMN LIST ||
| IDX_TB_ADDR_01 | USERID, NAME |
| IDX_TB_ADDR_02 | USERID, EMAIL |
| IDX_TB_ADDR_03 | UKEY |
| PK_TB_ADDR | USERID, UKEY |
userid 컬럼만으로 조회 될 때 데이터 건수가 많아 I/O 처리량이 너무 많이 발생함.
따라서 userid 컬럼과 추가 조건으로 구성된 결합 인덱스를 이용해 조회가 되야 효율적일 것임.
위 SQL은 :b2 조건 값이 null 이면서 email 컬럼 값으로 조회되는 경우 비효율적으로 수행되고 있음.
IDX_TB_ADDR_01 인덱스에서 userid 값에 해당하는 데이터를 찾은 후 테이블 액세스 후 email 값이
필터 처리되는 비효율이 있음.
그러므로 SQL을 패턴 별로 분리한 후 조회되는 조건에 따라 가장 유리한 SQL을 선택하여 수행하도록 해야 됨.
하나의 SQL로 해야된다면 UNION ALL 을 이용해서 실행계획을 분리시키는 방법도 있음.
UNION ALL 사용하는 방법은 분리해야 될 조건이 많다면 SQL이 길어지고 파싱시 부담이 될 수 있음.
||테스트||
|{code:sql}
-- 테이블 생성
drop table t1;
create table t1 as
select chr(65 + mod(level,4)) as c1
, chr(65 + mod(level,26)) || 'X' as c2
, level || 'X' as c3
from dual
connect by level <= 1000000
;
-- 인덱스 생성
create index t1_idx01 on t1(c1,c2);
create index t1_idx02 on t1(c1,c3);
-- 실행 쿼리
select /*+ index(t1 t1_idx01) */
count(*)
from t1
where c1 = '&b1'
and c2 like '&b2' || '%'
and c3 like '&b3' || '%'
;
-- 변수 설정 (b3 값 null 설정)
define b1 = A
define b2 = AX
define b3 = ''
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1347 (1)| 00:00:17 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| T1 | 10245 | 130K| 1347 (1)| 00:00:17 |
|* 3 | INDEX RANGE SCAN | T1_IDX01 | 10245 | | 25 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
-- 효율적으로 T1_IDX01 인덱스 액세스
-- 변수 설정 (b2 값 null 설정)
define b1 = A
define b2 = ''
define b3 = 4X
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 33315 (1)| 00:06:40 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| T1 | 4 | 52 | 33315 (1)| 00:06:40 |
|* 3 | INDEX RANGE SCAN | T1_IDX01 | 253K| | 574 (2)| 00:00:07 |
-----------------------------------------------------------------------------------------
-- t1_idx02 인덱스를 액세스하는 것이 효율적이지만 실행이 고정되어 있어서 T1_IDX01 인덱스 액세스 함.
-- 개선 후 SQL (SQL을 분리함)
select count(*)
from (select /*+ index(t1 t1_idx01) */
*
from t1
where 1 = &div
and c1 = '&b1'
and c2 like '&b2' || '%'
union all
select /*+ index(t1 t1_idx02) */
*
from t1
where 2 = &div
and c1 = '&b1'
and c3 like '&b3' || '%'
)
;
-- 변수 설정 (b3 값 null 설정)
define div = 1
define b1 = A
define b2 = AX
define b3 = ''
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 25 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | VIEW | | 10246 | | 25 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
|* 4 | INDEX RANGE SCAN | T1_IDX01 | 10245 | 51225 | 25 (0)| 00:00:01 |
|* 5 | FILTER | | | | | |
|* 6 | INDEX RANGE SCAN| T1_IDX02 | 253K| 2478K| 748 (1)| 00:00:09 |
---------------------------------------------------------------------------------
-- T1_IDX01 인덱스 액세스
-- 변수 설정 (b2 값 null 설정)
define div = 2
define b1 = A
define b2 = ''
define b3 = 4X
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | VIEW | | 5 | | 3 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
|* 4 | FILTER | | | | | |
|* 5 | INDEX RANGE SCAN| T1_IDX01 | 253K| 1239K| 574 (2)| 00:00:07 |
|* 6 | INDEX RANGE SCAN | T1_IDX02 | 4 | 40 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
-- T1_IDX02 인덱스 액세스
SQL을 조회하는 변수 값에 의해 추출되는 데이터 건수의 차이에 따라 다른 실행계획으로 수행되도록 유도하는 것을 말함.
예를 들면, 추출 데이터가 적은 경우는 인덱스 스캔으로 많은 경우는 Full Table Scan 수행.
| {code:sql} select * from cust where reg_date between :B1 and :B2 ; |
REG_DATE : 고객 가입날짜 컬럼
하루 평균 가입 고객수 : 500명
조회 패턴 : 1일 조회가 대부분. 한달에 한번은 최근 일년간의 데이터 추출.
조회 패턴에 따른 추출 데이터 건수 : 1일 조회 - 500건, 1년 조회 - 365일 * 500 = 182,500건
||테스트||
|{code:sql}
-- 테이블 생성
drop table t1;
create table t1 as
select to_char(sysdate + mod(level,10000),'YYYYMMDD') c1
, trunc(level/10000) c2
from dual
connect by level <= 1000000
;
-- 인덱스 생성
create index t1_idx01 on t1(c1);
-- 실행 쿼리
select /*+ index(t1 t1_idx01) */
*
from t1
where '&b1' = '&b2'
and c1 between '&b1' and '&b2'
union all
select /*+ full(t1) */
*
from t1
where '&b1' <> '&b2'
and c1 between '&b1' and '&b2'
;
-- 변수 설정 (시작일과 종료일이 동일)
define b1 = '20131201'
define b2 = '20131201'
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101 | 1919 | 100 (0)| 00:00:02 |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 100 | 1900 | 100 (0)| 00:00:02 |
|* 3 | INDEX RANGE SCAN | T1_IDX01 | 100 | | 3 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
|* 5 | TABLE ACCESS FULL | T1 | 100 | 1900 | 667 (3)| 00:00:09 |
-----------------------------------------------------------------------------------------
-- 시작일과 종료일이 동일하므로 상단 쿼리만 실행되어서 인덱스 액세스 함.
-- 변수 설정 (1년 조회)
define b1 = '20131201'
define b2 = '20141131'
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 46248 | 858K| 667 (100)| 00:00:09 |
| 1 | UNION-ALL | | | | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 46247 | 858K| 44734 (1)| 00:08:57 |
|* 4 | INDEX RANGE SCAN | T1_IDX01 | 46247 | | 130 (1)| 00:00:02 |
|* 5 | TABLE ACCESS FULL | T1 | 46247 | 858K| 667 (3)| 00:00:09 |
------------------------------------------------------------------------------------------
-- 시작일과 종료일이 다르므로 하단 쿼리만 실행되어서 풀테이블 스캔 함.
| {code:sql} SELECT name, TO_CHAR(last_captured,'yyyymmdd hh24:mi:ss') last_captured, datatype, value_string FROM DBA_HIST_SQLBIND WHERE sql_id = :sql_id ; |
NAME LAST_CAPTURED DATATYPE VALUE_STRING
-- 크게 두 가지 패턴으로 나뉜다는 것을 알 수 있음.
-- 두 가지 패턴에 따른 SQL 분리를 하면 됨.