7. 인덱스 스캔 효율
2011.03.08 18:15
I/O 튜닝의 중요점
- sequential 액세스의 선택도를 높이다
- random 액세스 발생량을 줄인다
(1) 비교 연산자 종류와 컬럼 순서에 따른 인덱스 레코드의 군집성
- 인덱스: 같은 값을 갖는 레코드( = ) 들이 항상 서로 군집
- 첫 번째 나타나는 범위검색 조건까지만 만족하는 인덱스 레코드는 모두 연속되게 모여 있지만,
- 그 이하 조건까지 만족하는 레코드는 비교 연산자 종류에 상관없이 흩어진다.
(2)인덱스 선행 컬림이 등치(=) 조건이 아닐 때 발생하는 비효율
- 인덱스 컬럼중 일부가 조건절에서 생략되거나 ‘=’ 조건이 아니더라도, 그것이 뒤쪽 컬럼일대는 비효율이 없다.
- 선행컬림이 조건절에 누락되거나 between, 부등호, like 같은 범위검색 조건이 사용되면 인덱스 스캔단계에서 비효율이 발생
select * from 매물아파트매매
where 아파트세세코드=’a0002’
and 평형=’59’
and 평형타입=’A’
and 인터넷매물 between ‘1’ and ‘2’
order by 입력일 desc
인덱스: 아파트 시세+평형+평형타입+인테냇 매물
아파트 시세코드 평형 평형타입 인터넷 매물a0001 15 A 0
a0001 21 B 1
a0002 59 A 1
a0002 59 A 2
a0003 22 A 0
a0003 59 D 3
a0004 23 A 1
a0004 33 D 2
인덱스 선행 컬럼이 모두 ‘=’ 조건일때는 필요한 범위만 스캔
인덱스: 인테넷 매물+아파트 시세+평형+평형타입
인터넷 매물 아파트 시세코드 평형 평형타입0 a0001 15 A
0 a0003 22 A
1 a0001 21 B
1 a0002 59 A
1 a0004 23 A
1 a0004 33 D
2 a0002 59 A
3 a0003 59 D
선두컬럼이 BETWEEN 이므로 결과값은 흩어져 있게되어 조건에 만족하지 않는 레코드 까지 스캔하고 서 버려야 하는 비효율이 발생
- 인덱스 스캔의 시작점 탐색시 인터넷 매물=1 AND 시세 <’A002’ AND 평형 <’59’ AND 평형타입 <’A’ 로
필터링 후 스캔시작 종료역시 조건 값에 따라 필터링후 스캔 종료
(3) BETWEEN 조건을 IN-List 로 바뀌었을 때 인덱스 스캔 비율
- IN 으로 변경
(2)의 예중 인터넷 매물 을 in 조건으로 바꾸면
select * from 매물아파트매매
where 아파트세세코드=’a0002’
and 평형=’59’
and 평형타입=’A’
and 인터넷매물 in (‘1’, ‘2’)
order by 입력일 desc
- 실행계획상 INLIST ITERATOR 가 발생하게된다
- 이것은 IN 조건을 = 조건으로 변경하여 UNION ALL 한것과 동일하므로
- ‘=’ 조건으로 스캔이 일어나게 되어 성능향상의 효과를 볼수 있다.
- BETWEEN 조건을 IN-LIST 조건으로 바꿀 때 주의 사항
- IN-LIST 개수가 많지 않아야 함
- 범위 스캔의 비효율은 사라지지만 인덱스 수직 탐색이 여러번 발생
- 인덱스 스캔과정에서 선택되는 레코드들이 서로 멀리 떨어져 있을때만 유용
- BETWEEN 조건의 선행컬럼이 많은 인덱스 리프블록을 스캔하더라도 I/O면에서는 소량일 경우가 많음
(인덱스 리프블록은 매우 많은 레코드가 담김)
(4) Index Skip Scan을 이용한 비효율 해소
- 인덱스 선두 컬림이 누락됐을 때뿐만 아니라 부등호,BETWEEN,like 같은 범위검색 조건일때도 유용하게 사용
자료 생성
create table month_sales_sum
as
select rownum cno
,'2008' || lpad(ceil(rownum/100000),2,'0') month
,decode(mod(rownum,12),1,'A','B') styp
,round(dbms_random.value(1000,100000),-2) samt
from dual
connect by level<=1200000;
create index month_sales_sum_idx1 on month_sales_sum(styp,month);
create index month_sales_sum_idx2 on month_sales_sum(month,styp);
각 조건별 결과
SQL> select count(*)
2 from month_sales_sum t
3 where styp='A'
4 and month between '200801' and '200812';
COUNT(*)
----------
100000
Execution Plan
----------------------------------------------------------
Plan hash value: 3159671310
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 292 (2)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
|* 2 | INDEX RANGE SCAN| MONTH_SALES_SUM_IDX1 | 111K| 1086K| 292 (2)| 00:00:04 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("STYP"='A' AND "MONTH">='200801' AND "MONTH"<='200812')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
28 recursive calls
0 db block gets
362 consistent gets
601 physical reads
0 redo size
411 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
idx1 사용
- cost: 292
- consistent gets: 362
SQL> select /*+ index ( t MONTH_SALES_SUM_IDX2 ) */ count(*)
2 from month_sales_sum t
3 where styp='A'
4 and month between '200801' and '200812';
COUNT(*)
----------
100000
Execution Plan
----------------------------------------------------------
Plan hash value: 1604684706
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 3473 (2)| 00:00:42 |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
|* 2 | INDEX RANGE SCAN| MONTH_SALES_SUM_IDX2 | 111K| 1086K| 3473 (2)| 00:00:42 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MONTH">='200801' AND "STYP"='A' AND "MONTH"<='200812')
filter("STYP"='A')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
3169 consistent gets
3089 physical reads
0 redo size
411 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
idx2 사용
- cost: 3473
- consistent gets: 3169
SQL> select count(*)
2 from month_sales_sum t
3 where styp='A'
4 and month in ('200801','200802','200803','200804','200805','200806','200807','200808','200809','200810','200811','200812');
COUNT(*)
----------
100000
Execution Plan
----------------------------------------------------------
Plan hash value: 2540619797
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 284 (2)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
| 2 | INLIST ITERATOR | | | | | |
|* 3 | INDEX RANGE SCAN| MONTH_SALES_SUM_IDX1 | 111K| 1086K| 284 (2)| 00:00:04 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("STYP"='A' AND ("MONTH"='200801' OR "MONTH"='200802' OR
"MONTH"='200803' OR "MONTH"='200804' OR "MONTH"='200805' OR "MONTH"='200806' OR
"MONTH"='200807' OR "MONTH"='200808' OR "MONTH"='200809' OR "MONTH"='200810' OR
"MONTH"='200811' OR "MONTH"='200812'))
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
393 consistent gets
0 physical reads
0 redo size
411 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
in-list 로 변경
- cost: 284
- consistent gets: 393
SQL> select /*+ index_ss ( t MONTH_SALES_SUM_IDX2 ) */ count(*)
2 from month_sales_sum t
3 where styp='A'
4 and month between '200801' and '200812';
COUNT(*)
----------
100000
Execution Plan
----------------------------------------------------------
Plan hash value: 3665529068
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 195 (26)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
|* 2 | INDEX SKIP SCAN| MONTH_SALES_SUM_IDX2 | 111K| 1086K| 195 (26)| 00:00:03 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MONTH">='200801' AND "STYP"='A' AND "MONTH"<='200812')
filter("STYP"='A')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
379 consistent gets
8 physical reads
0 redo size
411 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
index_ss 사용
- cost: 195
- consistent gets : 379
- idx1 사용
- cost 292
- consistent gets 362
- idx2 사용
- cost 3473
- consistent gets 3169
- in-list 사용 (idx2)
- cost 284
- consistent gets 393
- index_ss 사용 (idx2)
- cost 195
- consistent gets 379
(5) 범위검색 조건을 남욜할 때 발생하는 비효율
* like 조건을 기준으로 설명
쿼리1
select 고객id, 상품명, 지역...
from 가입상품
where 회사 = :com
and 지역 =:reg
and 상품명 like :prod || ‘%’;
쿼리2
select 고객 id, 상품명, 지역...
from 가입상품
where 회사 =:com
and 상품명 like :prod || ‘%’;
쿼리3
select 고객 id, 상품명, 지역,...
from 가입상품
where 회사=:com
and 지역 like ;reg || ‘%’
and 상품명 like :prod ||’%’
조건1
회사=’C70’
지역=’02’
상품=’보급'
조건2
회사=’C70’
상품=’보급'
인덱스
회사 + 지역 + 상품명
회사
지역
상품명
쿼리1
쿼리2
쿼리3
쿼리3
조건1
조건2
조건1
조건2
C60
03
일반
C70
02
고급
C70
02
보급1
V
V
V
V
C70
02
보급2
V
V
V
V
C70
02
일반
V
V
V
V
C70
02
일반
V
V
V
C70
02
일반
V
V
V
C70
02
일반
V
V
V
C70
02
일반
V
V
V
C70
03
고급
V
V
V
C70
03
보급1
V
V
C80
02
고급
V
V
- 쿼리3의 조건 1과 쿼리 1의 조건 1을 비교해보면 LIKE 연산을 사용함으로서 스캔 범위가 늘어남을 볼수 있으므로
- 무분별한 LIKE 조건의 사용을 자제 하는것이 좋다.
(6) 같은 컬럼에 두개의 범위검색 조건 사용 시 주의 사항
- 도서 table에 도서명으로 index가 있을경우
WHERE 도서명 like :book_nm || ‘%’
and 도서명 > :last_book_nm
:last_book_nm보다 큰 레코드를 찾고서 stop
and 도서명 > :last_book_nm
or-expansion을 이용하는 방법과 주의 사항
- use_concat 힌트를 사용하면 or 조건에 대한 expansion(union all 분기) 이 일어나게 되어 union all을 사용할때보다
sql코딩량을 줄일수 있음
- 주의 점
- 9i 까지
- i/o 비용 모델, cpu비용모델을 불문하고 뒤쪽 조건을 먼저 실행
- 10g 부터
- cpu비용 모델에서는 계산된 카디널리티가 낮은 쪽을 먼저 실행
뒤쪽조건을 먼저 처리하도록 하려면 ordered_predicates 힌트를 명시해야함
- rowid 를 concatenation하면 결과에 오류 발생
원하지 않는 결과가 나올수 있으므로 주의가 필요하다.
SQL> select greatest('AAAH+WAAJAAAHxTAA9','AAAH+WAAJAAAHxTAA+') from dual;
GREATEST('AAAH+WAA
------------------
AAAH+WAAJAAAHxTAA9
SQL> select greatest(chartorowid('AAAH+WAAJAAAHxTAA9'),chartorowid('AAAH+WAAJAAAHxTAA+')) from dual;
GREATEST(CHARTOROW
------------------
AAAH+WAAJAAAHxTAA+
SQL>
인덱스를 스캔하면서 rowid를 필터링 할 때 발생하는 비효율
and rowid > :last_rid
다른 액세스 조건만(도서명) 으로 리프 블록을 찾아가야하며 이때 같은 도서명을 가진 레코드가
많을 경우 비효율이 발생할수 있다
(7) Between 과 Like 스캠 범위 비교
SQL> create index month_sales_sum2_idx2 on month_sales_sum2(month,styp);
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.
SQL> select /*+ index( t month_sales_sum2_idx2 ) */ count(*)
2 from month_sales_sum2 t
3 where month between '200801' and '200802'
4 and styp='A';
COUNT(*)
----------
16667
Execution Plan
----------------------------------------------------------
Plan hash value: 1365354869
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 590 (2)| 00:00:08 |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
|* 2 | INDEX RANGE SCAN| MONTH_SALES_SUM2_IDX2 | 17237 | 168K| 590 (2)| 00:00:08 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MONTH">='200801' AND "STYP"='A' AND "MONTH"<='200802')
filter("STYP"='A')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
304 consistent gets
304 physical reads
0 redo size
413 bytes sent via SQL*Net to client
384 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 /*+ index( t month_sales_sum2_idx2 ) */ count(*)
from month_sales_sum2 t
where month like '2008%'
and styp='A'; 2 3 4
COUNT(*)
----------
16667
Execution Plan
----------------------------------------------------------
Plan hash value: 1365354869
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 590 (2)| 00:00:08 |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
|* 2 | INDEX RANGE SCAN| MONTH_SALES_SUM2_IDX2 | 17237 | 168K| 590 (2)| 00:00:08 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MONTH" LIKE '2008%' AND "STYP"='A')
filter("STYP"='A' AND "MONTH" LIKE '2008%')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
560 consistent gets
256 physical reads
0 redo size
413 bytes sent via SQL*Net to client
384 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 /*+ index( t month_sales_sum2_idx2 ) */ count(*)
from month_sales_sum2 t
where month >= '200801'
and month < '200803'
and styp='A';
2 3 4 5
COUNT(*)
----------
16667
Execution Plan
----------------------------------------------------------
Plan hash value: 1365354869
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 590 (2)| 00:00:08 |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
|* 2 | INDEX RANGE SCAN| MONTH_SALES_SUM2_IDX2 | 17237 | 168K| 590 (2)| 00:00:08 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MONTH">='200801' AND "STYP"='A' AND "MONTH"<'200803')
filter("STYP"='A')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
560 consistent gets
0 physical reads
0 redo size
413 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
-
between 연산자의 쿼리1은 2008년 1월 데이터를 모두 읽고 2월 데이터는 구분 ‘A’에서 멈춤
-
like 는 둘다 모두 읽음
-
쿼리3의 등호 역시 like와 같은 스캔량을 보임
범위 검색 조건의 스캔 시작점 결정 원리
- 실제 사용에서는 상황에 따라다르지만 적어도 between이 like보다
더 넓은 범위를 스캔하지는 않으므로 between을 사용하는것이 유리하다.
- 인덱스 스캔
- 브랜치 블록에서 뒤쪽 엔트리부터 스캔을 시작해 비교값보다 작은 값을 만나는 순간에 가리키는 하위 노드로 이동
- like 조건으로 검색할경우 ‘판매월=’2009’이고 판매구분=’B’인 레코드를 목표로 수직적탐색
- 하위노드를 탐색하다가 ’2009’보다 작은 ‘2008’||’12’||’B’레코드가 담긴 마지막 리프블록에 도달한후 멈춤
- between 일때는 ‘판매월=’200901’ 이고 판매구분 =’B;인 레코드를 목표로 수직적 탐색
- 하위노드를 탐색하다가 ‘200901’||’A’가 담긴 마지막 리프블록에 도달한후 멈춤
- like라도 인덱스에서 실제 찾아지는값 (‘200801’) 을 입력하면 스캔 범위를 줄일수 있으면
- between 이라도 실제 없는 값 (‘200800’) 으로 조회하면 like와 같은 스캔량을 가지게 됨
(8) 선분이력의 인덱스 스캔 비율
선분이력이란?
- 점이력
- 이력의 시작점만을 관리하는 모델 {고객번호+변경일자}
- 선분이력
- 시작지점과 종료시점을 함께 관리 {고객번호+시작일자+종료일자}
최근 이력의 마지막 종료일이 없으므로 종료일은 ‘99991231’
- 장점
- 선분형태로 관리하면 between 조인을 이용하여 간단하게 이력시점을 조회할수 있다.
- ‘20090505’ between b.시작일 and b.종료일 형태인 간단한 쿼리로 처리할수 있어 성능상 유리해질수도 있다
- 단점
- 이력이 추가될때마다 최종 이력의 종료일자도 같이 변경해 주어야함으로 DML성능이나
데이터 관리 프로그램등이 복잡해질수 있다(과거이력 일괄 보정) - 개체 무결성을 사용자가 직접 관리해 주어야 함
선분이력 기본 조회 패턴
SELET 연체개월수, 연체금액
FROM 고객별연체금액
where 고객번호 = ‘123’
and :dt between 시작일 and 종료일
- 현 시점은 ‘99991231’등으로 두어야 함으로 현시점을 검색할때도
- and 종료일=’99991231’ 만으로 검색가능
- 하지만 자료가 미리 등록되어있는 등의 경우 ( 가격변경등) 에는 to_char(sysdate,’yyyymmdd’)등을 활용하여야 한다.
{시작일+종료일} 구성일 때 최근 시점 조회
where 고객번호=’123’
and ‘20050131’ between 시작일 and 종료일
을 풀어쓰면
where 고객번호 =’123’
and 시작일 <=’20050131’
and 종료일 >=’20050131’
- 이 되어 인덱스 스캔시 = 조건을 찾은후 시작일 컬럼의 <= 을 모두 스캔하고 종료일 >= 조건을 모두 스캔하므로
- 두 조건 모두 범위를 줄이는데 기여하지 못하게 된다.
- 하지만 두 조건 모두 index 컬럼에 포함되어있어 테이블 random 액세스 없이 필터링 할수 있지만 이력이 많은 고객일경우
- 한건을 스캔하기 위해 많은 인덱스를 스캔해야 한다.
- 그럴경우 index_desc 힌트에 rownum<=1 조건을 추가해주만 index를 거꾸로 스캔하면 빠르게 찾을수 있다.
{시작일+종료일} 구성일 때 과거 시점 조회
- index_desc 힌트를 주고 rownum<=1 조건을 추가해 주면 스캔범위를 한건으로 줄일수 있음
{종료일+시작일} 구성일 때 최근 시점 조회
- index: { 고객번호+종료일+시작일 }
where 고객번호=’123’
and ‘20050131’ between 시작일 and 종료일
- = 조건 다음 종료일이 >= 이므로 2005년 1월 31일 이후 이력 레코드를 모두 스캔해야하지만
- rownum<=1 조건을 추가해 준다면 스캔범위를 1건으로 줄일수 있음
{종료일+시작일} 구성일때 과거 시점 조회
where 고객번호=’123’
and ‘20020930’ between 시작일 and 종료일
- ‘=’ 조건 다음 종료일 >= 조건이므로 2002년 9월 30일 이력을 모두 스캔하여야 하지만
- rownum<=1 조건을 추가해 준다면 1건으로 줄일수 있음
중간시점 조회
- 중간시점 역시 위와 같이 인덱스 구성에따라
- index_desc 힌트 + rownum<=1 을 사용하거니 rownum<=1을 사용하면 빠르게 찾을수 있음
선분 이력 스캔 효율을 높이는 방법 요약
- 최근데이터를 주로 조회한다면 {종료일+시작일}
- 과거데이터를 주로 조회한다면 {시작일+종료일} 순으로 구성하는 것이 효과적
- {시작일+종료일} 일 경우 index_desc 힌트와 rownum<=1조건을 추가
- {종료일+시작일} 일 경우 rownum<=1 조건만 추가해주면 된다
실험 - 김범석 추가
CREATE TABLE IDX_DESC_TEST ( USER_ID VARCHAR2(20), STT_DTM DATE, END_DTM DATE);
COMMIT;
DECLARE
I NUMBER := 1;
J NUMBER := 1;
BEGIN
WHILE I < 500001 LOOP
INSERT INTO IDX_DESC_TEST SELECT '1'|| LPAD(MOD( I, 2) + 1, 5, '0') AS USER_ID, TO_DATE(SYSDATE, 'YY-MM-DD') + I AS STT_DTM, TO_DATE(SYSDATE, 'YY-MM-DD') + (I + J) AS END_DTM FROM DUAL;
I := I + 1;
END LOOP;
END;
/
COMMIT;
-- 1.인덱스 => 고객번호 + 시작일 + 종료일
CREATE INDEX IDX_ID_STT_END ON IDX_DESC_TEST(USER_ID, STT_DTM, END_DTM);-- 2.인덱스 => 고객번호 + 종료일 + 시작일
CREATE INDEX IDX_ID_END_STT ON IDX_DESC_TEST(USER_ID, END_DTM, STT_DTM);
SELECT * FROM IDX_DESC_TEST;
-- 1.인덱스 => 고객번호 + 시작일 + 종료일
-- 시작일 + 종료일 구성일 때 최근 시점 조회
SELECT *
FROM IDX_DESC_TEST
WHERE USER_ID = '100002' AND '3131-08-07' BETWEEN STT_DTM AND END_DTM;/*
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 31 | 930 | 251 (1)| 00:00:04 |
|* 1 | INDEX RANGE SCAN| IDX_ID_END_STT | 31 | 930 | 251 (1)| 00:00:04 |
-----------------------------------------------------------------------------------
*/
SELECT *
FROM IDX_DESC_TEST
WHERE USER_ID = '100002' AND STT_DTM <= '3131-08-07' AND END_DTM >= '3131-08-07';/*
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 31 | 930 | 251 (1)| 00:00:04 |
|* 1 | INDEX RANGE SCAN| IDX_ID_END_STT | 31 | 930 | 251 (1)| 00:00:04 |
-----------------------------------------------------------------------------------
*/
SELECT /*+ INDEX_DESC(A IDX_ID_STT_END) */ *
FROM IDX_DESC_TEST A
WHERE USER_ID = '100002' AND '3131-08-07' BETWEEN STT_DTM AND END_DTM AND ROWNUM <= 1;/*
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 34 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | INDEX RANGE SCAN DESCENDING| IDX_ID_STT_END | 31 | 930 | 34 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
*/
-- 2.인덱스 => 고객번호 + 종료일 + 시작일
-- 시작일 + 종료일 구성일 때 과거 시점 조회
SELECT *
FROM IDX_DESC_TEST
WHERE USER_ID = '100002' AND '2011-03-19' BETWEEN STT_DTM AND END_DTM;/*
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 300 | 3 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_ID_STT_END | 10 | 300 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
*/
SELECT *
FROM IDX_DESC_TEST
WHERE USER_ID = '100002' AND STT_DTM <= '2011-03-19' AND END_DTM >= '2010-03-19';/*
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 300 | 3 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_ID_STT_END | 10 | 300 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
*/
SELECT /*+ INDEX_DESC(A IDX_ID_END_STT) */ *
FROM IDX_DESC_TEST A
WHERE USER_ID = '100002' AND '2011-03-19' BETWEEN STT_DTM AND END_DTM AND ROWNUM <= 1;/*
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 125 (1)| 00:00:02 |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | INDEX RANGE SCAN DESCENDING| IDX_ID_END_STT | 10 | 300 | 125 (1)| 00:00:02 |
-----------------------------------------------------------------------------------------------
*/-- 1. 최신날짜로 검색을 했을시에는 앞에서 만들어 졌던 IDX_ID_END_STT 인덱스로 범위스캔
-- 2. 과거날짜로 검색을 했을시에는 앞에서 만들어 졌던 IDX_ID_STT_END 인덱스로 범위스캔
-- 한다는 사실을 알게되었다.
-- 결국 둘이 비교했을 경우
SELECT /*+ INDEX_DESC(A IDX_ID_STT_END) */ *
FROM IDX_DESC_TEST A
WHERE USER_ID = '100002' AND '2011-03-19' BETWEEN STT_DTM AND END_DTM AND ROWNUM <= 1;/*
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 3 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | INDEX RANGE SCAN DESCENDING| IDX_ID_STT_END | 10 | 300 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
*/
SELECT /*+ INDEX_DESC(A IDX_ID_END_STT) */ *
FROM IDX_DESC_TEST A
WHERE USER_ID = '100002' AND '2011-03-19' BETWEEN STT_DTM AND END_DTM AND ROWNUM <= 1;/*
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 125 (1)| 00:00:02 |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | INDEX RANGE SCAN DESCENDING| IDX_ID_END_STT | 10 | 300 | 125 (1)| 00:00:02 |
-----------------------------------------------------------------------------------------------
*/
SELECT /*+ INDEX_DESC(A IDX_ID_STT_END) */ *
FROM IDX_DESC_TEST A
WHERE USER_ID = '100002' AND '3131-08-07' BETWEEN STT_DTM AND END_DTM AND ROWNUM <= 1;/*
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 34 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | INDEX RANGE SCAN DESCENDING| IDX_ID_STT_END | 31 | 930 | 34 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
*/
SELECT /*+ INDEX_DESC(A IDX_ID_END_STT) */ *
FROM IDX_DESC_TEST A
WHERE USER_ID = '100002' AND '3131-08-07' BETWEEN STT_DTM AND END_DTM AND ROWNUM <= 1;/*Plan hash value: 1689508773
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 11 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | INDEX RANGE SCAN DESCENDING| IDX_ID_END_STT | 31 | 930 | 11 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
*/
(9) Access Predicate 와 Filter Predicate
- dbms_xplan패키지를 통해 실행 계획을 출력해보면 Predicate 정보를 확인할 수있다.
SQL> set autotrace traceonly explain;
select /*+ ordered use_nl(e) index(e emp_x01) */ *
from dept d, emp e
where d.loc = 'CHICAGO'
and e.deptno = d.deptno
and e.job like 'SALE%'
and e.job between 'A' and 'Z'
and e.sal >= 1000
and e.ename like '%A%'
and trim(e.ename) = 'ALLEN'
and e.comm >= 300
and to_char(e.hiredate, 'yyyymmdd') like '198102%' ;
SQL> SQL> 2 3 4 5 6 7 8 9 10 11
Execution Plan
----------------------------------------------------------
Plan hash value: 3070931621
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 57 | 4 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 37 | 2 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 57 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | DEPT_IDX | 1 | | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_IDX | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("E"."ENAME" LIKE '%A%' AND TRIM("E"."ENAME")='ALLEN' AND
TO_CHAR(INTERNAL_FUNCTION("E"."HIREDATE"),'yyyymmdd') LIKE '198102%' AND
"E"."SAL">=1000 AND "E"."COMM">=300)
4 - access("D"."LOC"='CHICAGO')
5 - access("E"."DEPTNO"="D"."DEPTNO" AND "E"."JOB" LIKE 'SALE%')
filter("E"."JOB" LIKE 'SALE%' AND "E"."JOB">='A' AND "E"."JOB"<='Z')
SQL>
1. 인덱스 단계 Access Predicate 5
2. 인덱스 단계 Filter Predicate 5
3. 테이블 단계 Filter Predicate 1
1. 인덱스 단계에서의 Access Predicate
- 인덱스 스캔 범위를 결정하는데에 영향을 미치는 조건절
- 범위 검색 조건을 뒤따르는 조건이거나 선행 컬럼이 조건에서 누락될지라도 액세스 범위를 결정하는데 기여
- 수직적 탐색과정에서 모든 인덱스 컬럼을 비교 조건으로 사용
- 영향을 안 미치는 경우
- 좌변 컬럼을 가공한 조건절
trim(e.ename)=’ALLEN’ - 왼쪽 ‘%’ 또는 양쪽 ‘%’ 기호를 사용한 LIKE 조건절
ename like ‘%A%’ - 같은 컬럼에 대한 조건절이 두개 이상일때, 액세스 조건으로 선택( job like ‘SALES%’) 되지 못한 조건
job between ‘A’ and ‘Z’
2. 인덱스 단계에서의 Filter Predicate
- 테이블로의 액세스 여부를 결정짓는 조건
- 첫번째 나타나는 범위 검색 조건부터 이후 모든 조건절 컬럼들이 포함하며
조건절에서 누락된 컬럼 뒤쪽에 놓인 인덱스 컬럼들도 포함
3. 테이블 단계에서의 Filter Predicate
- 테이블을 액세스 하고 나서 최종 결과집합으로의 포함여부를결정짓는 조건절
- 인덱스를 경유하지 않고 테이블 전체를 스캔할때는 테이블 단계에서의 Filter Predicate만 나타 남
(10) Index Fragmentation
- B*Tree 인텍스의 ‘B’ 는 ‘Balanced’의 약자로 인덱스 루트에서 리프 블록까지 어떤 값으로 탐색하더라도
읽는 블록 수가 같음을 의미 (모든 리프블록까지의 높이(height) 가 동일) - 불균형은 없지만 index fragmentation에 의한 index skew 또는 sparse현상이 생길수 있음
Index skew
- 인덱스 엔트리가 왼쪽또는 오른쪽에 치우치는 현상
- 대량의 delete작업을 마치고 나면 왼쪽에 있는 리프 블록들은 텅비고 오른쪽 블록은 꽉차는 등의 현상이 나타 날수도 있음
- 텅빈 인덱스 블록은 커밋하는 순간 freelist로 반환되지만 인덱스 구조상에는 그대로 남께 되면
- 언제든 재사용이 가능하지만 다시 채워질때 까지 인덱스 스캔(대개 Index Full Scan) 효율이 낮아지게 된다.
- 통 계성 테이블등과 같이 매일 delete & insert 가 있을경우
delete , insert 작업을 완료후 commit 을 하게 되면 왼쪽 블록은 항상 빈상태로 남아있으므로
delete , commit , insert , commit 순으로 작업을 하게 된다면 삭제된 블록이 재사용되어 효율적이다. - insert & commit & delete & commit 는 의미가 없다.
Index Sparse
- 인덱스 블록 전반에 걸쳐 밀도 (density)가 떨어지는 현상
- 블록이 텅비면 곧바로 freelist 로 반환되 언제든 재사용 되지만 index sparse는
지워진 자리에 새로운 값이 입력되지 않으면 영영 재사용되지 않을 수도 있다.
Index Rebuild
- Fragmentation 때문에 인덱스 크기가 계속 증가하고 스캔 효율이 나쁠대는
coalesce 명령을 수행하여 처리
- 여러 인덱스 블록을 하나로 병합하고, 결과로 생긴 빈블록들은 freelist에 반환한다.
단 인덱스 세그먼트에 할당된 미사용공간 은 반환되지 않는다.
- index fragmentation을 해소하면서 공간까지 반환할경우 사용 단 ASSM에서만 동작한다.
- 공간은 반환하지 않으므로 coalesce와 같은 명령어가 된다.
- colaesce와 shrink는 레코드를 건건이 지웠다가 입력하므로 작업량이 많을 경우
rebuild를 사용하는것이 효율적일수 있다.
alter index t_idx rebuild online;
- 이와 같이 인덱스 구조를 슬림화하면 저장효율과 스캔효율은 좋아지지만 블록 공간이 전혀없으면
인덱스 분할이 자주 발생해 DML성능을 떨어뜨릴수 있으므로 상황에 맞게 사용하여야한다. - 인덱스 분할에 의해 Shared 모두 enq:TX-index contention 대기 이벤트가 자주나타면
pctfree를 높이고 나서 인덱스를 rebuild를 하면 경합을 줄일수 있다.
(인덱스에서 pctfree는 인덱스를 처음 생성하거나 rebuild때만 적용)
인덱스 rebuild를 고려 사항
- 인덱스 분할에 의한 경합이 현저히 높을때
- 자주 사용되는 인덱스 스캔 효율을 높일때 (NL조인에서 반복 액세스되는 인덱스 높이가 증가했을때)
- 대량의 delete작업후 다시 입력될때까지 장기간이 필요할때
- 레코드수는 일정한대 인덱스가 계속 커질때
인덱스 freelist
- 테이블
- pctfree와 pctused는 각각 freelist에서 제외되는 시점과, 다시 등록되는 시점을 지정
- 인덱스
- 값이 입력될때마다 freelist를 참조하지 않아도 되며 분할로 빈블록이 필요할 때만 참조, pctused 파라미터는 없음
PCTFREE
- 테이블
- 블록에 더이상 INSERT가 발생하지 못하도록 FREELIST에서 제외되는 시점이며 남겨진 공간은 UPDATE를 위해 사용
- 인덱스
- 지정한 비율만큼 공간을 남겨두었다가 나중에 INSERT 를 위해 사용
PCTUSED
- FREELIST에서 제거된 테이블 블록에 빈공간이 일정 수준 이상 확보되었을경우 다시 FREELIST에
등록하기 위한 파라미터
- PCTFREE가 10이고 PCTUSED가 80이면 freelist에서 제거된 블록에
10%정도 delete가 발생하는 순간 해당 블록을 다시 freelist에 등록
- {100-pctfree} 만큼의 공간이 차면 freelist에서 제거
pctused만큼만 남기고 레코드가 모두 지워질때 다시 freelist에 등록
- 인덱스에서의 freelist는 인덱스 분할에 사용 가능 빈 블록들을 관리
- delete에 의해 지워진 인덱스 블록은 커밋 시점에 freelist에 반환되지만
- insert 시점에 다시 값이 입력되더라도 곧바로 freelist에서 제거되지 않음.
- freelist에 그대로 두었다가 인덱스 분할때문에 freelist를 스캔하는 프로세스에 의해 정리
- (freelist에서 얻은 블록이 비어있지 않으면 다른 블록을 재요청하기 전에 일단 해당 블록을 freelist에서 제거- 커밋 시점에 수행해야할 일량을 최소화 하기 위함)
- 오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
- 작성자: 남송휘 (휘휘)
- 최초작성일: 2011년 3월 8일
- 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
- 문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^
| 번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
|---|---|---|---|---|
| 20 | 5. 인덱스를 이용한 소트 연산 대체 | 멋진넘 | 2011.06.13 | 3360 |
| 19 | 6. IOT, 클러스터 테이블 활용 | 휘휘 | 2011.02.26 | 2764 |
| 18 | 6. 히스토그램 | 실천하자 | 2011.04.24 | 10955 |
| 17 | 6. 조인 제거 | 멋진넘 | 2011.05.30 | 4605 |
| 16 |
6. Sort Area를 적게 사용하도록 SQL 작성
 | 실천하자 | 2011.06.13 | 8878 |
| » | 7. 인덱스 스캔 효율 [1] | 휘휘 | 2011.03.08 | 16955 |
| 14 | 7. 비용 | 휘휘 | 2011.05.02 | 6182 |
| 13 | 7. OR-Expansion | 멋진넘 | 2011.05.31 | 8733 |
| 12 | 7. Sort Area 크기 조정 | 실천하자 | 2011.06.13 | 15117 |
| 11 | 8. 인덱스 설계 | 멋진넘 | 2011.03.06 | 8356 |
| 10 | 7. 조인을 내포한 DML 튜닝 | 실천하자 | 2011.04.03 | 7192 |
| 9 | 8. 통계정보 Ⅱ [1] | 멋진넘 | 2011.04.29 | 31578 |
| 8 | 8. 공통 표현식 제거 | darkbeom | 2011.06.06 | 5232 |
| 7 |
9. 비트맵 인덱스
| 실천하자 | 2011.03.05 | 12376 |
| 6 |
8. 고급 조인 테크닉-1
[1] | darkbeom | 2011.04.03 | 13294 |
| 5 | 9. Outer 조인을 Inner 조인으로 변환 | darkbeom | 2011.06.06 | 7861 |
| 4 |
8. 고급 조인 테크닉-2
| suspace | 2011.04.05 | 7027 |
| 3 | 10. 실체화 뷰 쿼리로 재작성 | suspace | 2011.06.07 | 5540 |
| 2 | 11. 집합 연산을 조인으로 변환 | suspace | 2011.06.07 | 6005 |
| 1 | 12. 기타 쿼리 변환 [3] | 실천하자 | 2011.06.02 | 6658 |
선분 이력 스캔하는 부분 테스트 추가했습니다. 맞는지 정확히는 모르겠어요.
한번 보시고 틀리점이나 개선점 있으면 지워주시거나 추가해주세요 ^^ (당췌 잠이 안오더라고요;;)