1. I/O 튜닝의 핵심 원리
2. (1) 비교 연산자 종류와 커럼 순서에 따른 인덱스 레코드의 군집성
3. (2) 인덱스 선행 컬림이 등치(=) 조건이 아닐 때 발생하는 비효율
4. BETWEEN 조건을 IN-LIST로 바꾸었을 때 인덱스 스캔 효율
5. Index Skip Scan을 이용한 비효율 해소
6. (5)범위검색 조건을 남용할 때 발생하는 비효율
7. (6) 같은 컬럼에 두 개의 범위검색 조건 사용 시 주의 사항
8. Between과 Like 스캔 범위 비교
9. 선분이력의 인덱스 스캔 효율
10. 시작일 + 종료일 구성일 때 최근 시점 조회
11. (9) Access Predicate와 Filter Predicate
12. (10) Index Fragmentation

• Sequential Access는 레코드간 논리적 또는 물리적인 순서를 따라 차례대로 읽어 나가는 방식
• Random 액세스는 레코드간 논리적, 물리적인 순서를 따르지 않고 한 건을 읽기 위해 한 블록씩 접근(=touch)하는 방식

I/O 튜닝의 핵심 원리

1. Sequential 액세스의 선택도를 높인다
2. Random 액세스 발생량을 줄인다.

(1) 비교 연산자 종류와 커럼 순서에 따른 인덱스 레코드의 군집성

• 인덱스 레코드는 '같은 값을 갖는' 레코드들이 항상 서로 군집해 있음
• '같은 값을 갖는다'라고 하면 '=' 비교가 전제되므로, 만약 비교 연산자가 '=' 조건이 아닐 때는 인덱스 레코드도 서로 흩어진 상태 일 수 있음

where col1 = 1 and col2 = 'A' and col3 = '나' and col4 = 'a'
- 인덱스 구성 컬럼이 모두 '=' 조건으로 비교될 때는 조건을 만족하는 레코드들이 모두 연속되게 모여 있다.(5~7번까지)

where col1 = 1 and col2 = 'A' and col3 = '나' and col4 >= 'a';
- 선행 컬럼은 모두 '='이고 맨 마지막 컬럼만 범위검색 조건(부등호, between, like)일 때도
조건을 만족하는 레코드가 모두 모여 있다(5~10번)

where col1 = 1 and col2 = 'A' and col3 between '가' and '다' and col4 = 'a'
- 중간 컬럼이 범위검색 조건일 때는 col1부터 col3까지 세 조건만을 만족하는 인덱스 레코드는
서로 모여 있지만(2~12번) col4 족건까지 만족하는 레코드는 흩어지게 된다(2,3,5,6,7,11번)

where col1 = 1 and col2 <='B' and col3 = '나' and col4 between 'a' and 'b'
- 두 번째 컬럼 col2가 범위검색 조건인 경우는 col1부터 col2가지 두 조건만을 만족하는 인덱스 레코드는
 서로 모이지만(2~16번) col3와 col4 조건까지 만족하는 레코드는 흩어지게 된다.(5,6,7,8,9,14,15번)

(2) 인덱스 선행 컬림이 등치(=) 조건이 아닐 때 발생하는 비효율

• Sequential 액세스 효율은 같은 결과 건수를 내는데 얼마나 적은 레코드를 읽느냐로 효율성을 판단 할 수 있음
• Sequential 액세스에 다른 선택도는 인덱스 컬럼이 조건절에 모두 등치(=) 조건으로 사용될 때 가장 높음
• 리프 브록을 스캔하면서 읽은 레코드는 하나도 필터링 되지 않고 모두 테이블로 액세스로 이어지므로 비효율은 전혀 없음
• 인덱스 컬럼 중 일부가 조건절에서 생략되거나 '=' 조건이 아니더라도, 뒤쪽 컬럼일 때는 비효율이 없음

- 인덱스 : 아파트시세코드 + 평형 + 평형타입 + 인터넷매물
where 아파트시세코드 = :a
where 아파트시세코드 = :a and 평형 = :b
where 아파트시세코드 = :a and 평형 = :b and 평형타입 = :c
where 아파트시세코드 = :a and 평형 = :b and 평형타입 between :c and :d

• 인덱스 선행 컬럼이 조건에 누락되거나 between, 부등호, like 같은 범위검색 조건이 사용되면 인덱스를 스캔하는 단계에서 비효율이 발생

- 인덱스 : 아파트시세코드 + 평형 + 평형타입 + 인터넷매물
select   해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드
from    매물아파트매매
where   아파트시세코드='A0101450900056'
and     평형 = '59'
and     평형타입 = 'A'
and     인터넷매물 between '1' and '2'
order by 입력일 desc
- 인덱스매물이 between 조건이지만 선행 컬럼들이 모두 '=' 조건이기 때문에 전혀 비효율 없이 조건을 만족하는 2건을 빠르게 찾았다. <p.135 그림 1-38 참조>
- 인덱스 선행 컬럼이 모두 '=' 조건일 때 필요한 범위만 스캔하고 멈출 수 있는 것은, 조건을 만족하는 레코드가 모두 한데 모여 있기 때문.

- 인덱스 : 인터넷매물 + 아파트시세코드 + 평형 + 평형타
- 인덱스 선두 컬럼 인터넷매물에 between 연산자를 사용하면 나머지 조건을 만족하는 레코드들이 인터넷 매물 값범위로 뿔뿔이
  흩어져 있다. 조건을 만족하지 않는 레코드까지 스캔하고서 버려야 하는 비효율 발생.

BETWEEN 조건을 IN-LIST로 바꾸었을 때 인덱스 스캔 효율

• 범위검색 컬럼이 맨 뒤로 가도록 인덱스를 아파트시세코드 + 평형 + 평형타입 + 인터넷매물 순으로 변경하면 좋겠지만 운영중인 시스템에서 인덱스 구성을 바꾸끼는 어려우므로 이 때, BETWEEN을 IN-LIST로 바꿔주면 효과가 큰 경우가 있다.

select 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드
  from 매물아파트매매
 where 인터넷매물 in ('1', '2')
   and 아파트시세코드 = 'A01011350900056'
   and 평형 = '59'
   and 평형타입 = 'A'
 order by 입력일 desc

between 조건을 IN-LIST 조건으로 바꿀 때 주의 사항

• IN-LIST 개수가 많지 않아야 함
• 인덱스 수직 탐색이 여러 번 발생하기 때문에 IN-LIST 개수가 많을 때는, 리프 블록을 추가로 스캔하는 비효율보다 IN-LIST 조건 때문에 브랜치 블록을 반복 탐색하는 비효율이 더 클 수 있고, 인덱스 높이(height)가 높을 대 특히 그렇다.
• 인덱스 스캔 과정에서 선택되는 레코드들이 서로 멀리 떨어져 있을 때만 유용
• between 조건인 선행 컬럼 때문에 많은 인덱스 리프 블록을 스캔하지만 거기서 선택되는 레코드는 소량일 대라야 IN-LIST로의 변환이 효과를 낸다.

Index Skip Scan을 이용한 비효율 해소

• 인덱스 선두 컬럼이 누락됐을 때뿐만 아니라 부등호, between, like같은 범위검색 조건일 때도 Index Skip Scan이 유용하게 사용될 수 있고, 데이터 상황에 따라서는 '=' 조건 컬럼들을 인덱스 선두에 위치시킨 것에 버금가는 효과를 얻음
• 선두 컬럼이 between이고 검색 조건을 만족하는 데이터들이 서로 멀리 떨어져 있을 경우 효과.

- 월별로 10만 개 판매데이터 입력
- 'A'가 10만개, 'B'가 110만개
create table 월별고객별판매집계
as
select rownum 고객번호
     , '2008' || lpad(ceil(rownum/100000), 2, '0') 판매월
     , decode(mod(rownum, 12), 1, 'A', 'B') 판매구분
     , round(dbms_random.value(1000,100000), -2) 판매금액
from   dual
connect by level <= 1200000 ;

create index 월별고객별판매집계_IDX1 on 월별고객별판매집계(판매구분, 판매월);

select count(*)
from   월별고객별판매집계 t
where  판매구분 = 'A'
and    판매월 between '200801' and '200812';

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
      1   SORT AGGREGATE (cr=281 pr=269 pw=0 time=95791 us)
 100000    INDEX RANGE SCAN 월별고객별판매집계_IDX1 (cr=281 pr=269 pw=0 time=...)
- IDX1 인덱스를 스캔하면서 281개 블록 I/O 발생

create index 월별고객별판매집계_IDX2 on 월별고객별판매집계(판매월, 판매구분);

select /*+ index(t 월별고객별판매집계_IDX2) */ count(*)
from   월별고객별판매집계 t
where  판매구분 = 'A'
and    판매월 between '200801' and '200812';

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
      1   SORT AGGREGATE (cr=3090 pr=3100 pw=0 time=981731 us)
 100000    INDEX RANGE SCAN 월별고객별판매집계_IDX2 (cr=3090 pr=3100 pw=0 time=...)
- IDX2 인덱스를 스캔하면서 3,090개 블록 I/O가 발생

select /*+ index(t 월별고객별판매집계_IDX2) */ count(*)
from   월별고객별판매집계 t
where  판매구분 = 'A'
and    판매월 in ( '200801', '200802', '200803', '200804', '200805', '200806'
                 , '200807', '200808', '200809', '200810', '200811', '200812' ) ;

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
      1   SORT AGGREGATE (cr=314 pr=2 pw=0 time=115212 us)
 100000    INLIST ITERATOR  (cr=314 pr=2 pw=0 time=800042 us)
 100000     INDEX RANGE SCAN 월별고객별판매집계_IDX2 (cr=314 pr=2 pw=0 time=...)
- between 조건을 IN-List로 변환, 블록 I/O 개수가 314개로 감소
- 인덱스 브랜치 블록을 10번 반복 탐색하는 비효율이 있긴 하지만 리프 블록을 스캔할 때의 비효율을 제거, 1/10 수준으로 성능 개선

select /*+ INDEX_SS(t 월별고객별판매집계_IDX2) */ count(*)
from   월별고객별판매집계 t
where  판매구분 = 'A'
and    판매월 between '200801' and '200812' ;

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
      1   SORT AGGREGATE (cr=300 pr=0 pw=0 time=120292 us)
 100000    INDEX SKIP SCAN 월별고객별판매집계_IDX2 (cr=300 pr=0 pw=0 time=...)
- 큰 비효율 없이 단 300 블록만 읽었다.

(5)범위검색 조건을 남용할 때 발생하는 비효율

ex)
* 회사, 지역, 상품명을 입력하여 '가입상품' 테이블에서 데이터를 조회하는 프로그램.
1. 회사는 반드시 입력
2. 지역은 입력하지 않을 수도 있다.
3. 상품명은 단어 중 일부만 입력하고도 조회 가능

# 쿼리 1 : 회사, 지역, 상품명 모두 입력
SELECT ....
FROM  가입상품
WHERE 회사 = :com
AND   지역 = :reg
AND   상품명 LIKE :prod || '%'

# 쿼리 2 : 회사, 상품명만 입력
SELECT ....
FROM  가입상품
WHERE 회사 = :com
AND   상품명 LIKE :prod || '%'

- 인덱스 : 회사 + 지역 + 상품명
- 각각의 스캔 범위

# 하나의 쿼리로 작성
SELECT ....
FROM  가입상품
WHERE 회사 = :com
AND   지역 LIKE :reg || '%'
AND   상품명 LIKE :prod || '%'

- 회사, 지역, 상품명이 모두 입력했을 때와 지역을 입력하지 않았을 때의 스캔 범위
- 지역을 입력한 경우 인덱스 스캔 범위가 늘어난다.
- 인덱스 컬럼에 범위검색 조건을 남용하면 첫 번째 범위검색 조건에 의해 스캔 범위가 대부분 결정되며, 그 다음을 따르는 조건부터는 스캔 범위를 줄이는 데에 크게 기여하지 못하므로 성능상 불리할 수 있음

select *
from  gis데이터
where gis_위도 betwwen :승객위도 - 1 and :승객위도 + 1
and   gis_경도 between :승객경도 - 1 and :승객경도 + 1
and   sysdate between gis_시작일자 and gis_종료일자

gis데이터_x01 : gis_위도 + gis_경도 + gis_시작일자 + gis_종료일자 + gis_지역코드 + gis_위치명

문제점 :
- 조건절이 모두 between 범위검색 조건
- 인덱스 스캔 범위는 인덱스 선두 컬럼인 'gis_위도' 컬럼에 대한 between 조건에 의해 결정.
- gis데이터 테이블에는 당일치만 보관.
- 당일 영업 시작시에는 빠르지만 밤 시간에는 고객의 특정 위치 기준으로 위도상 좌우 1km 이내에 평균 100만 개 레코드.
- 인덱스 스캔 원리상 현재 데이터 모델로는 과도한 인덱스 스캔 범위를 줄일 방법은 없다.

튜닝 :
- 1km 이내를 한 번에 조회하지 말고 50m, 200m, 1km 순으로 나눠서 쿼리
- 가장 가까운 데 위치한 하나의 택시를 찾는게 목적.
- 세 구간으로 나누어 쿼리하면 대부분 첫 번째 쿼리에서 찾게 된다.
- 인덱스 스캔량을 1/20로 줄일 수 있다.
- 50m, 200m, 1km 이내의 가장 가까운 택시를 찾을 때 위의 쿼리의 1을 0.05, 0.2, 1 로 바꾸어 쿼리.
- 추가로 최종 건수 한 건만을 우편번호와 조인하도록 쿼리 변경.

(6) 같은 컬럼에 두 개의 범위검색 조건 사용 시 주의 사항

예제

• 도서 조회용 프로그램 개발
• '오라클'을 키워드로 입력하면, '오라클'로 시작하는 모든 도서가 조회
• 한 화면에 10개씩 출력

select *
from (
  select rownum rnum, 도서번호, 도서명, 가격, 저자, 출판사, isbn
  from (
    select 도서번호, 도서명, 가격, 저자, 출판사, isbn
    from   도서
    where  도서명 like :book_nm || '%'
    order by 도서명
  )
  where rownum <= 100
)
where rnum >= 91  --> 10 페이지만 출력
- 도서명 컬럼에 인덱스가 있다면 첫 번째 rownum 조건에 해당하는 레코드만 읽고 멈춘다. count(stopkey)오퍼레이션 적용.
- 사용자들이 앞쪽 일부 레코드만 주로 볼 경우 효과적

- 뒤쪽 어느 페이지로 이동하더라도 빠르게 조회되도록 구현해야 한다면?
- 아래는 첫번째 페이지를 출력하고 나서 '다음' 버튼을 누를 경우 예
select *
from (
    select /*+ index(도서 도시명_idx) */
           rowid rid, 도서번호, 도서명, 가격, 저자, 출판사, isbn
    from   도서
    where  도서명 like :book_nm || '%'
    and    도서명 = :last_book_nm    -- 이전 페이지에서 출력된 마지막 도서명
    and    rowid  > :last_rid        -- 이전 페이지에서 출력된 마지막 도서의 rowid
    union all
    select /*+ index(도서 도시명_idx) */
           rowid rid, 도서번호, 도서명, 가격, 저자, 출판사, isbn
    from   도서
    where  도서명 like :book_nm || '%'
    and    도서명 > :last_book_nm    -- 이전 페이지에서 출력된 마지막 도서명
)
where   rownum <= 10
;
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
     10   COUNT STOPKEY (cr=382 pr=0 pw=0 time=218 us)
     10    VIEW  (cr=382 pr=0 pw=0 time=197 us)
     10     UNION-ALL  (cr=382 pr=0 pw=0 time=180 us)
      1      FILTER  (cr=4 pr=0 pw=0 time=141 us)
      1       TABLE ACCESS BY INDEX ROWID 도서 (cr=4 pr=0 pw=0 time=108 us)
      1        INDEX RANGE SCAN 도시명_IDX (cr=3 pr=0 pw=0 time=137 us)(Object ID 52955)
      9      TABLE ACCESS BY INDEX ROWID 도서 (cr=378 pr=0 pw=0 time=56979 us)
      9       INDEX RANGE SCAN 도시명_IDX (cr=377 pr=0 pw=0 time=56916 us)(Object ID 52955)
- union all 위쪽 브랜치에서 한건, 아래쪽에서 9건 읽었다.
- 아래쪽 인덱스 스캔단계에서 377 블록을 읽었다.
- 도서명에 대한 범위검색 조건이 두 개인데 그 중 like 조건을 인덱스 액세스 조건으로 사용했기 때문.
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=10)
   4 - filter(:LAST_BOOK_NM LIKE :BOOK_NM||'%')
   6 - access("도서명"=:LAST_BOOK_NM)
       filter("도서명" LIKE :BOOK_NM||'%' AND ROWID>CHARTOROWID(:LAST_RID))
   8 - access("도서명" LIKE :BOOK_NM||'%')
       filter("도서명">:LAST_BOOK_NM AND "도서명" LIKE :BOOK_NM||'%')
- '오라클' 도서를 처음부터 스캔하다가 :last_book_nm 보다 큰 9개 레코드를 찾고서 멈추었다.
- 뒤쪽 페이지로 많이 이동할수록 그 비효율은 커진다.

- 해결방안 : '도서명 > :last_book_nm' 조건이 인덱스 액세스 조건으로 사용하도록 한다.
select *
from (
    select /*+ index(도서 도시명_idx) */
           rowid rid, 도서번호, 도서명, 가격, 저자, 출판사, isbn
    from   도서
    where  도서명 like :book_nm || '%'
    and    도서명 = :last_book_nm    -- 이전 페이지에서 출력된 마지막 도서명
    and    rowid  > :last_rid        -- 이전 페이지에서 출력된 마지막 도서의 rowid
    union all
    select /*+ index(도서 도시명_idx) */
           rowid rid, 도서번호, 도서명, 가격, 저자, 출판사, isbn
    from   도서
    where  rtrim(도서명) like :book_nm || '%'
    and    도서명 > :last_book_nm    -- 이전 페이지에서 출력된 마지막 도서명
)
where   rownum <= 10
;

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
     10   COUNT STOPKEY (cr=7 pr=0 pw=0 time=143 us)
     10    VIEW  (cr=7 pr=0 pw=0 time=128 us)
     10     UNION-ALL  (cr=7 pr=0 pw=0 time=117 us)
      1      FILTER  (cr=4 pr=0 pw=0 time=93 us)
      1       TABLE ACCESS BY INDEX ROWID 도서 (cr=4 pr=0 pw=0 time=72 us)
      1        INDEX RANGE SCAN 도시명_IDX (cr=3 pr=0 pw=0 time=76 us)(Object ID 52955)
      9      TABLE ACCESS BY INDEX ROWID 도서 (cr=3 pr=0 pw=0 time=123 us)
      9       INDEX RANGE SCAN 도시명_IDX (cr=2 pr=0 pw=0 time=75 us)(Object ID 52955)

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=10)
   4 - filter(:LAST_BOOK_NM LIKE :BOOK_NM||'%')
   6 - access("도서명"=:LAST_BOOK_NM)
       filter("도서명" LIKE :BOOK_NM||'%' AND ROWID>CHARTOROWID(:LAST_RID))
   8 - access("도서명">:LAST_BOOK_NM)
       filter(RTRIM("도서명") LIKE :BOOK_NM||'%')
- 부등호 조건을 만족하는 첫 번째 레코드부터 스캔을 시작했기 때문에 스캔한 블록수는 2.

OR-Expansion을 이용하는 방법과 주의 사항

• use_concat 힌트를 사용
• OR 조건에 대한 expansion(union all 분기)이 일어나면 뒤쪽 조건절이 먼저 실행된다.

select /*+ index(도서 도시명_idx) use_concat ordered_predicates */
       rowid rid, 도서번호, 도서명, 가격, 저자, 출판사, isbn
from   도서
where  도서명 like :book_nm || '%'
and  ((도서명 > :last_book_nm)
       or
      (도서명 = :last_book_nm and rowid > :last_rid) )
and    rownum <= 10
;

• 버전에 따라 실행되는 순서가 달라진다.
• 9i까지는 I/O 비용 모델, CPU 비용 모델을 불문하고 뒤쪽에 있는 조건 값을 먼저 실행
• 10g CPU 비용 모델에서는 계산된 카디널리티가 낮은 쪽을 먼저 실행
• ordered_predicates 힌트 : 10g에서 값 분포에 상관없이 항상 뒤쪽에 있는 조건식이 먼저 처리

rowid를 concatenation하면 결과에 오류 발생

• 아래처럼 작성하면 결과가 달라짐

select /*+ index(도서 도시명_idx) */
       rowid rid, 도서번호, 도서명, 가격, 저자, 출판사, isbn
from   도서
where  도서명 like :book_nm || '%'
and    도서명 >= :last_book_nm
and    lpad(도서명, 50) || rowid > lpad(:last_book_nm, 50) || :last_rid
and    rownum <= 10
;
=>
and    lpad(도서명, 50) || rowidtochar(rowid) > lpad(:last_book_nm, 50) || :last_rid
- 문자형으로 변환된 rowid는 rowid 값 그대로 비교할 때와 정렬순서가 다르다.

select greatest('AAAH+WAAJAAAHxTAA9', 'AAAH+WAAJAAAHxTAA+') from dual;

GREATEST('AAAH+WAA
------------------
AAAH+WAAJAAAHxTAA9

 select greatest( chartorowid('AAAH+WAAJAAAHxTAA9')
              , chartorowid('AAAH+WAAJAAAHxTAA+') ) from dual;

GREATEST(CHARTOROW
------------------
AAAH+WAAJAAAHxTAA+

인덱스를 스캔하면서 rowid를 필터링할 때 발생하는 비효율

• '이전' 페이지에서 출력된 마지막 레코드와 이름이 같은 도서를 찾는 쿼리는 비효율이 없는가?
• rowid를 가지고 '='조건으로 바로 엑세스할 땐 어떤 액세스보다 빠르지만 인덱스를 스캔하면서 rowid를 필터링할 때는 아니다.
• 인덱스 rowid는 리프블록에만 있다.
• 이를 필터링하려면 다른 액세스 조건만으로 리프 블록을 찾아가 거기서 스캔을 시작해 rowid를 필터링.
• '도서' 테이블에 같은 도서명을 가진 레코드가 '아주' 많다면 뒤 페이지로 이동할수록 비효율.
• 가장 완벽한 구현은 인덱스 뒤쪽에 PK컬럼을 붙이고 쿼리를 수정하는 것이다.
• 하지만 인덱스 뒤에 PK 컬럼을 붙이기는 쉽지 않다.(PK가 다중 컬럼으로 구성된 경우는 더 복잡)
• 중복 값이 아주 많은 경우가 아니라면 rowid를 이용하는 방안이 현실적이다.

drop table t purge;

set feedback off

create table t as select * from all_objects ;

insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;

select count(*) from t;

  COUNT(*)
----------
    797200

update t set object_id = rownum;

column rid new_value rid
select * from (
  select rowid rid from t where owner = 'SYS' order by rowid desc
) where rownum <= 5 ;

RID
------------------
AAAM7cAAGAAAFv8AAH
AAAM7cAAGAAAFv8AAG
AAAM7cAAGAAAFv8AAF
AAAM7cAAGAAAFv8AAE
AAAM7cAAGAAAFv8AAD

set autotrace on statistics
select /*+ index(t t_owner_idx) */ rowid
from   t
where  owner = 'SYS'
and    rowid >= chartorowid('&rid')
and    rownum <= 5 ;

ROWID
------------------
AAAM7cAAGAAAFv8AAD
AAAM7cAAGAAAFv8AAE
AAAM7cAAGAAAFv8AAF
AAAM7cAAGAAAFv8AAG
AAAM7cAAGAAAFv8AAH

Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
        205  consistent gets
          0  physical reads
          0  redo size
        554  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

set autotrace off

create index t_owner_idx2 on t(owner, object_id);

column oid new_value oid
select * from (
  select object_id oid from t where owner = 'SYS' order by rowid desc
) where rownum <= 5 ;

OID
----------
    756976
    756960
    756944
    756928
    756912

set autotrace on statistics
select /*+ index(t t_owner_idx2) */ object_id
from   t
where  owner = 'SYS'
and    object_id >= &oid
and    rownum <= 5 ;

OBJECT_ID
----------
    756912
    756913
    756914
    756915
    756916

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        498  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

Between과 Like 스캔 범위 비교

select * from 월별고객별판매집계
where 판매월 like '2009%';

select * from 월별고객별판매집계
where 판매월 between '200901' and '200912';

• 검색을 위해 입력한 값과 테이블의 실제 데이터 상황에 따라 둘 간의 인덱스 스캔량이 다를 수 있다.
• between을 사용한다면 적어도 손해는 않본다.

• Index Skip Scan에서 이용한 '월별고객별판매집계' 테이블에서 2009년 1월과 2월 데이터가 추가로 입력.
• 판매구분으로는 'A'와 'B' 두 개의 값이 8.3%와 91.7% 비중.

<쿼리 1>
select count(*)
from  월별고객별판매집계
where 판매월 between '200901' and '200902'
and   판매구분 = 'A';

<쿼리 2>
select count(*)
from  월별고객별판매집계
where 판매월 like '2009%'
and   판매구분 = 'A';

<쿼리 3>
select count(*)
from  월별고객별판매집계
where 판매월 >= '200901'
and   판매월 < '200903'
and   판매구분 = 'A';

• 위의 세 쿼리는 결과는 같지만 I/O 발생량에서 큰 차이를 보인다.
• 쿼리 1 : 2009년 1월 데이터는 모두, 2009년 2월 데이터는 판매구분이 'A'인 데이터만 읽는다.
• 쿼리 2 : 2009년 1월과 2월 데이터를 모두 읽는다.
• 쿼리 3 : '200902' || 'A' 구간만 읽고서 멈추지 못해 결국 like 쿼리과 같은 스캔량을 보인다.

• 'A'와 'B' 값의 비중이 91.7%와 8.3%로 입력된 상태에서 판매구분이 'B'인 레코드를 찾는 경우
• (스캔량의 차이를 크게 나타내 보이려고 바꾸는 것일 뿐 스캔 범위 결정 원리와는 무관)

<쿼리 4>
select count(*)
from  월별고객별판매집계
where 판매월 between '200901' and '200902'
and   판매구분 = 'B';

<쿼리 5>
select count(*)
from  월별고객별판매집계
where 판매월 like '2009%'
and   판매구분 = 'B';

<쿼리 6>
select count(*)
from  월별고객별판매집계
where 판매월 between '200900' and '200902'
and   판매구분 = 'B';

<쿼리 7>
select count(*)
from  월별고객별판매집계
where 판매월 like '200901%'
and   판매구분 = 'B';

• 쿼리 4 : 2009년 2월 데이터는 모두 읽더라도 1월 데이터는 판매구분이 'B'인 값만 읽는다.
• 쿼리 5 : 2009년 1월과 2월 데이터 모두 읽는다. 1월 데이터를 모두 스캔하는 이유는 리프블록에 실제 없는 '2009'으로 조회 했기 때문.
• 쿼리 6 : 쿼리 5와 스캔량은 같다. between이라고 하더라도 쿼리 시작값을 '29900'으로 입력하고 조회하면 LIKE와 스캔량이 값음
• 쿼리 7 : like지만, 인덱스에서 실제 찾아지는 값을 입력하면 스캔 범위를 줄일 수 있음

범위검색 조건이 스캔 시작점 결정 원리

• 범위검색 조건 뒤를 따르는 조건절은 스캔 범위를 줄이는 데에 영향을 미칠 수도 있고 그렇지 않을 수도 있다.
• 사용자가 조건 비교를 위해 입력한 값이나 테이블의 실제 데이터 상황에 따라 다르다.
• 적어도 between이 like보다 더 넓은 범위를 스캔하는 경우는 없다.(between 사용하라)

• 인덱스 컬럼 조건에 대해 범위검색 조건이 나타나면 이후 조건은 인덱스 스캔 범위를 줄여주지 못해 비효율 발생.
• 위와 같이 범위검색 조건 뒤에 사용된 조건들도 제한적이나마 스캔 범위를 줄이는데 기여할 수 있다.

• 쿼리 5의 like인 경우
  • (판매원 = '2009' 이고 판매구분 = 'B')인 레코드를 목표로 수직적 탐색.
  • 브랜치 블록에서 뒤쪽 엔트리부터 스캔을 시작해 비교 값보다 작은 값을 만나는 순간 거기서 가리키는 하위 노드로 이동.
  • 그림 1-45와 같은 상황에서는 '200812' || 'B'인 레코드가 담긴 가장 마지막 리프 블록에 도달.
  • 만약 그 값이 포함되지 않는다면 바로 다음 리프 블록의 처음 레코드 키 값이 '200901' || 'A'인 경우이다.
  • 수직 탐색 과정에서 사용한 비교 조건이 '200812' || 'B' 와 '200901' || 'A' 사이 값이므로 이 블록부터 스캔한다면 원하는 값을 모두 읽을 수 있다.
  • 이 때문에 불필요하게 '2009' || 'A' 구간까지 읽는다.
  • 여기서는 판매구분 = 'B' 조건이 스캔 범위를 줄이는데에 기여하지 못했다.

• 쿼리 4의 between의 경우
  • (판매월 = '200901' 이고 판매구분 = 'B')인 레코드를 목표로 수직적 탐색.
  • 브랜치 블록에서 뒤쪽 엔트리부터 스캔하다가 위 조건보다 작은 값을 만나는 순간 거기서 가리키는 하위 노드로 이동.
  • 그러다 보면 그림 1-45에서 '200901' || 'B'인 첫 번째 레코드가 담긴 리프 블록에 도달하거나, '200901' || 'A'인 레코드가 담긴 가장 마지막 리프 블록에 도달하게 된다.
  • 여기서는 판매구분 = 'B' 조건이 스캔 범위를 줄이는 데에 큰 기여를 한다.

• 키 포인트는 실제 테이블에 존재하는 값을 수직적 탐색 조건으로 사용했다는 점이다.
• 같은 between이더라도 쿼리 6과 같이 실제 테이블에 없는 데이터 값을 입력하고 조회한 경우에는 '200812' || 'B' 인 레코드가 담긴 가장 마지막 리프 블록부터 스캔을 시작하게 되므로 불필요하게 '200901' || 'A' 구간까지 읽게 된다.
• like 조건이더라도 쿼리 7과 같이 실제 테이블에 있는 데이터 값을 입력하고 조회할 때는 판매구분 = 'B' 조건이 스캔 범위를 줄이는 데에 큰 역할을 한다.

선분이력의 인덱스 스캔 효율

• 항상 두 개의 부등호 조건을 함께 사용하는 선분이력에선 데이터 특성상 두 번째 부등호 조건이 스캔 범위를 줄이는 데 전혀 도움을 주지 못한다.

선분이력이란?

• 점이력
  • 고객의 변경이력을 관리할 때 이력의 시작시점만을 관리하는 것
  • PK : 고객번호 + 변경일자
  • 쿼리가 복잡하다
  • PK 제약을 설정하는 것만으로 개체 무결성이 완벽히 보장 된다.

• 선분이력
  • 시작시점과 종료시점을 관리하는 것
  • PK : 고객번호 + 시작일자 + 종료일자(99991231 or 99991231235959)
  • 쿼리가 간단해 진다
  • 이력이 추가될 때마다 기존 최종 이력의 종료일자도 같이 변경해 주어야 한다.
  • DML 성능이 나빠지며 이력 데이터를 관리하는 프로그램이 복잡해 진다.
  • 개체 무결성을 사용자(애플리케이션)에서 직접 관리해 주어야 한다.
  • (예를 들어, 5/5~5/18, 5/15~6/23 두 개의 선분이 입력되면 5/15~5/18 구간에 중복 발생)
  • 선분이 겹치거나 끊어지지 않도록 방지해야 하므로 프로그래밍할 때 동시성 제어 기법을 철저히 적용해야 한다.
  • 사용자가 직접 데이터를 입력/수정하는 과정에서 정합성이 깨지는 문제는 근본적인 해결이 불가능하다(트리거를 이용하지 않는 한)
  • RDBMS 설계 사상에 맞지 않게 PK 값이 변경된다.
  • PK 제약만으로 개체 무결성이 보장되지 않으니 인덱스 스캔 효율이라도 높이기 위해 통상 PK 구성에 시작일자와 종료일자가 모두 포한되도록 설계하기 때문.
  • (고객번호+시작일자) 또는 (고객번호+종료일자)로 PK를 구성한 상태에서 update/insert 순서를 잘 조절하면 PK 값이 변경되지 않도록 구현할 수 있지만, 그때는 시작일자와 종료일자를 모두 포함한 인덱스를 별도로 생성해 주어야 한다.

• 선분이력 기본 조회 패턴

- 과거, 현재, 미래 임의 시점을 조회
:dt between 시작일 and 종료일

- 현재 시점을 조회할 경우(종료일을 '99991231'로 넣는다고 약속한 경우)
1. 선분이력 테이블에 정보를 미리 입력해 두는 경우
to_char(sysdate, 'yyyymmdd') between 시작일 and 종료일
2. 미리 입력해 두지 않는 경우
종료일 = '99991231'

시작일 + 종료일 구성일 때 최근 시점 조회

• 인덱스 : 고객번호 + 시작일 + 종료일

select *
from  고객별연체금액
where 고객번호 = '123'
and   '20050131' between 시작일 and 종료일;
=>
select *
from  고객별연체금액
where 고객번호 = '123'
and   시작일 <= '20050131'
and   종료일 >= '20050131';

• '=' 조건인 고객번호 다음 인덱스 컬럼(시작일)이 '<=' 조건이므로 2005년 1월 31일 이전의 많은 이력 레코드를 모두 스캔.
• 종료일 조건은 시작일 보다 훨씬 적은 범위지만 선두 컬럼이 부등호 조건이기 때문에 스캔 범위를 줄이는데 기여하지 못한다.
• 종료일이 인덱스 컬럼에 포함되 있기 때문에 테이블 Random 액세스 없이 필터링.
• 최종 레코드는 한 건이지만 많은 인덱스 레코드를 스캔하는 문제가 있다.

• index_desc 힌트를 주고 rownum <= 1 조건을 추가하여 개선.
• 시작일 컬럼에 desc 옵션을 준다면 rownum <= 1 조건만 사용.

select /*+ index_desc(a idx_x01) */ *
from  고객별연체금액
where 고객번호 = '123'
and   '20050131' between 시작일 and 종료일
and   rownum <= 1;

시작일 + 종료일 구성일 때 과거 시점 조회

• 인덱스 : 고객번호 + 종료일 + 시작일

select *
from  고객별연체금액
where 고객번호 = '123'
and   '20050131' between 시작일 and 종료일;
=>
select *
from  고객별연체금액
where 고객번호 = '123'
and   시작일 <= '20050131'
and   종료일 >= '20050131';

• =' 조건인 고객번호 다음 인덱스 컬럼(종료일)이 '<=' 조건이므로 2005년 1월 31일 이후 소량 이력 레코드만 스캔.
• 인덱스 스캔량이 얼마 되지 않지만 rownum <= 1 조건을 추가하여 개선.

select *
from  고객별연체금액
where 고객번호 = '123'
and   '20050131' between 시작일 and 종료일
and   rownum <= 1;

"종료일 + 시작일" 구성일 때 최근 시점 조회

• 인덱스 : 고객번호 + 종료일 + 시작일

select *
from  고객별연체금액
where 고객번호 = '123'
and   '20050131' between 시작일 and 종료일;
=>
select *
from  고객별연체금액
where 고객번호 = '123'
and   시작일 <= '20050131'
and   종료일 >= '20050131';

• '=' 조건인 고객번호 다음 인덱스 컬럼(종료일)이 '<=' 조건이므로 2005년 1월 31일 이후 소량 이력 레코드만 스캔.
• 인덱스 스캔량이 얼마 되지 않지만 rownum <= 1 조건을 추가하여 개선.

select *
from  고객별연체금액
where 고객번호 = '123'
and   '20050131' between 시작일 and 종료일
and   rownum <= 1;

"종료일 + 시작일" 구성일 대 과거 시점 조회

• 인덱스 : 고객번호 + 종료일 + 시작일

select *
from  고객별연체금액
where 고객번호 = '123'
and   '20020930' between 시작일 and 종료일;
=>
select *
from  고객별연체금액
where 고객번호 = '123'
and   시작일 <= '20020930'
and   종료일 >= '20020930';

• '=' 조건인 고객번호 다음 인덱스 컬럼(종료일)이 '<=' 조건이므로 2002년 9월 30일 이후의 많은 이력 레코드를 모두 스캔
• 스캔량해야 할 범위가 아무리 넓더라도 rownum <= 1 조건을 추가하여 개선.

select *
from  고객별연체금액
where 고객번호 = '123'
and   '20050131' between 시작일 and 종료일
and   rownum <= 1;

중간 시점 조회

• 중간 시점 이력을 조회할 때는 인덱스 구성을 어떻게 하든 어느 정도 비효율을 감소.
• 인덱스 구성이 (고객번호 + 시작일 + 종료일) 일 경우는 index_desc 힌트 및 rownum <= 1 조건으로 개선.
• 인덱스 구성이 (고객번호 + 종료일 + 시작일) 일 경우는 rownum <= 1 조건으로 개선.

선분이력 스캔 효율을 높이는 방법 요약

• 선분이력처럼 between 검색 조건이 사용될 때는 어느 시점을 주로 조회하느냐에 따라 인덱스 전략을 달리 가져가라.
• 최근 데이터를 주로 조회할 경우는 (종료일 + 시작일)순으로 구성
• 과거 데이터를 주로 조회한다면 (시작일 + 종료일) 순으로 구성
• (시작일 + 종료일) 일 때는 index_desc 힌트와 rownum <= 1 조건을 추가
• (종료일 + 시작일) 일 때는 rownum <= 1 조건만 추가
• 중간지점을 조회할 때도 위의 방식 사용.
• 미래 시점 데이터를 미리 입력하는 경우가 없다면, 현재 시점 데이터를 조회할 때는 (종료일 = '99991231') 조건을 사용하는 것이 효과적.

(9) Access Predicate와 Filter Predicate

• explain plan 명령을 통해 실행계획을 수집하고 dbms_xplan 패키지(@?/rdbms/admin/utlxpls.sql 참조)를 통해 실행계획을 확인해 보면 Predicate 정보를 확인할 수 있다.
• 오라클 9i부터 plan_table에 추가된 access_predicates 와 filter_predicates 컬럼으로부터 가져온 값.
• 10gR2부터는 Autotrace 명령을 통해서도 Predicate 정보를 출력해 볼 수 있다

create index emp_x01 on emp(deptno, job, sal, ename, mgr, comm);

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%' ;

Execution Plan
----------------------------------------------------------
Plan hash value: 346692358

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    57 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    37 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |         |     1 |    57 |     5   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL        | DEPT    |     1 |    20 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | EMP_X01 |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_CHAR(INTERNAL_FUNCTION("E"."HIREDATE"),'yyyymmdd') LIKE
              '198102%')
   3 - filter("D"."LOC"='CHICAGO')
   4 - access("E"."DEPTNO"="D"."DEPTNO" AND "E"."JOB" LIKE 'SALE%' AND
              "E"."SAL">=1000 AND "E"."COMM">=300 AND "E"."SAL" IS NOT NULL)
       filter("E"."ENAME" LIKE '%A%' AND "E"."JOB" LIKE 'SALE%' AND
              TRIM("E"."ENAME")='ALLEN' AND "E"."JOB">='A' AND "E"."JOB"<='Z' AND
              "E"."SAL">=1000 AND "E"."COMM">=300)

• 인덱스를 경유해 테이블을 액세스할 때는 최대 3가지 Predicate정보가 나타날 수 있다.
  • 인덱스 단계에서의 Access Predicate (id=4 access 부분)
  • 인덱스 단계에서의 Filter Predicate (id=4 filter 부분)
  • 테이블 단계에서의 Filter Predicate (id=1 filter 부분)

• 인덱스를 경유하지 않고 테이블 전체를 스캔할 때
  • 테이블 단계에서의 Filter Predicate (id=3 filter 부분)

• '인덱스 단계에서의 Access Predicate'는 인덱스 스캔 범위를 결정하는 데에 영향을 미치는 조건을 의미.
• 인덱스 컬럼에 대한 조건절은, 설령 범위검색 조건을 뒤따르는 조건(sal >= 1000)이거나 선행 컬럼이 조건절에서 누락될지라도 (comm >= 300), 액세스 범위를 결정하는 데에 기여하므로 대부분 Access Predicate에 포함
• 아래의 경우는 Access Predicate에서 제외 (스캔 범위 결정에 전혀 영향을 미치지 않는다)
  • 좌변 컬럼을 가공한 조건절 : trim(e.ename) = 'ALLEN'
  • 왼쪽 '%' 또는 양쪽 '%' 기호를 사용한 like 조건절 : ename like '%A%'
  • 같은 컬럼에 대한 조건절이 두 개 이상일 때, 인덱스 액세스 조건으로 선택되지 못한 다른 조건절 : job betwwen 'A' and 'Z'

• '인덱스 단계에서의 Filter Predicate'는 테이블로의 액세스 여부를 결정짓는 조건절을 의미.
• 첫 번째 나타나는 범위검색 조건부터 이후 모든 조건절 컬럼들이 여기에 포함되며, 조건절에서 누락된 컬럼 뒤쪽에 놓인 인덱스 컬럼들도 포함한다.
• '테이블 액세스 단계에서의 Filter Predicate'(id 3, 4)는 테이블을 액세스하고 나서 최종 결과집합으로의 포함여부를 결정짓는 조건절을 의미

(10) Index Fragmentation

• 오라클에서는 B*Tree 인덱스의 'B'는 'Balanced'의 약자로서, 인덱스 루트에서 리프 블록까지 어떤 값으로 탐색하더라도 읽는 블록 수가 같음을 의미.
• 루트로부터 모든 리프 블록까지의 높이(height)가 동일.
• Index Fragmentation에 의해 Index Skew 또는 Sparse 현상이 생기는 경우는 종종 있다. (인덱스 스캔 효율에 나쁜 영향을 미칠 수 있다.)

Index Skew

• 인덱스 엔트리가 왼쪽 또는 오른쪽에 치우치는 현상.
• 인덱스 왼쪽에 있는 리프 블록들은 텅비는 반명 오른쪽 블록들은 꽉 찬 상태가 된다.
• 텅빈 인덱스 블록은 커밋하는 순간 freelist로 반환되지만 인덱스 구조상에는 그대로 남는다.
• 상위 브랜치에서 해당 리프 블록을 가리키는 엔트리가 그대로 남아 있어 인덱스 정렬 순서상 그곳에 입력될 새로운 값이 들어오면 언제든 재사용될 수 있다.
• 재사용되더라도 이 경우에는 곧바로 freelist에서 제거되지 않으며 나중에 빈 블록을 찾기 위해 freelist를 스캔하는 프로세스에 의해 정리된다.
• 새로운 값이 하나라도 일력되기 전 다른 노드에서 인덱스 분할이 발생하면 이들 블록이 재사용된다.
• 이때 상위 브랜치에서 해당 리프 블록을 가리키는 엔트리가 제거돼 다른 쪽 브랜치의 자식 노드로 이동하고, freelist에서도 제거 된다.
• 레코드가 모두 삭제된 블록은 이처럼 언제든 재사용 가능하지만 문제는 다시 채워질 때까지 인덱스 스캔 효율이 낮다는데 있다.

create table t as select rownum no from dual connect by level <= 1000000 ;

create index t_idx on t(no) pctfree 0;

delete from t where no <= 500000 ;

commit;

set autotrace on statistics

- 맨 왼쪽부터 스캔하도록 조건 부여
select * from t where no > 0 and rownum <= 1;

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1001  consistent gets
          0  physical reads
          0  redo size
        420  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

• 위와 같은 Index Skew 때문에 성능이 나빠지는 경우는 대개 Index Full Scan 할 때다
• 대량의 데이터를 매일 지웠다가 새로 입력하는 통계성 테이블일 때는 Index Skew가 발생하지 않도록 트랜잭션 패턴에 신경을 써야 한다.

예제1)

• 보관주기가 3일인 일별고객별판매집계 테이블, 매일 10시에 트랜잭션 수행
  • PK 인덱스 : 판매일자 + 고객번호
  • 지난 데이터를 삭제 후 집계 데이터를 인서트 후 커밋
  • PK 인덱스 왼쪽에 놓인 상당수 리프 블록들이 delete 문을 통해 비워진 상태
  • (commit 전까지는 freelist 반환되지 않아 insert 과정에서 재사용 못함)
  • 다음날 다시 insert시 재사용 되겠지만 delete문으로 인해 지워진 블록들이 새로 발생.
  • delete 직후에 commit을 수행하여 insert 과정에서 재사용 되도록 변경.
    (업무의 중요도와 트랜잭션이 실패했을 때 재상 가능한지 등에 따라 판단)

예제2)

• 문장단위로 커밋을 하지만 insert를 먼저 수행하고 delete를 나중에 수행한 경우
  • insert문에 의해 Skew가 해소 되었다가 곧 이은 delete문에 의해 왼쪽 리프 노드가 또다시 지워진다.
  • insert문과 delete문 수행순서를 바꾸지 않는한 해결되지 않는다.

Index Sparse

• 인덱스 블록 전반에 걸쳐 밀도(density)가 떨어지는 현상.

drop table t;

create table t as select rownum no from dual connect by level <= 1000000 ;

create index t_idx on t(no) pctfree 0 ;

select /*+ index(t) */ count(*) from t where no > 0;

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2001  consistent gets
          0  physical reads
          0  redo size
        424  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

delete from t where mod(no, 10) < 5 ;

commit;

select /*+ index(t) */ count(*) from t where no > 0;

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2001  consistent gets
          0  physical reads
          0  redo size
        424  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

• 지워진 자리에 인덱스 정렬 순서에 따라 새로운 값이 입력되면 그 공간은 재사용되지만 위와 같이 대량의 delete 작업이 있고 난 후 한동안 인덱스 스캔 효율이 낮다.
• Index Sparse는 지워진 자리에 새로운 값이 입력되지 않으면 영영 재사용되지 않을 수 있다.
• 총 레코드 건수가 일정한데도 인덱스 공간 사용량이 계속 커지는 것은 대개 이런 현상에 기인한다.

Index rebuild

• coalesce : 여러 인덱스 블록을 하나로 병합(merge)하고, 그 결과로서 생긴 빈 블록들은 freelist에 반환한다.
• 인덱스 세그먼트에 할당된 미사용 공간(HWM 아래쪽에서 freelist에 등록된 블록과 HWM 위쪽 미사용 블록)은 반환되지 않는다.

alter index t_idx coalesce;

• shrink space : : Index Fragmentation을 해소하면서 공간까지 반환, ASSM에서만 작동

alter index t_idx shrink space;

• shrink space compact : 공간을 반환하지 않는다. coalesce와 같다.

alter index t_idx shrink space compact;

• coalesce와 shrink는 레코드를 건건히 지웠다가 다시 입력하는 방식을 사용.
• 작업량이 많을 때는 rebuild 명령을 사용.

alter index t_idx rebuild;
alter index t_idx rebuild online;

• 인덱스 구조를 슬림화하면 저장 효율이나 스캔 효율은 좋아지지만 일반적으로 인덱스 블록에는 어느 정도 공간을 남겨두는 것이 좋다.
• (맨 우측 리프 블록에만 값이 입력되는 right-growing 인덱스일 때는 pctfree를 0으로 설정하는 것이 좋다)
• 인덱스 블록에 공간이 전혀 없으면 인덱스 분할이 자주 발생해 DML 성능을 떨어 뜨린다.
• 인덱스 분할 때문에 Shared 모드 enq: TX - index contention 대기 이벤트가 자주 나타난다면 pctfree를 높게 설정하는 것을 고려해 볼 수 있다.( pctfree를 높게 설정하는 것만으로는 전혀 효과가 없다.)
• 인덱스에서의 pctfree는 인덱스를 처음 생성하거나 rebuild할 때만 적용되므로 인덱스 경합을 줄이려면 pctfree를 높이고 나서 인덱스를 rebuild해야 한다
• 하지만 효과는 일시적, 언젠가 빈 공간이 다시 채워지기 때문.
• 아래와 같이 예상효과가 확실할 때만 인덱스 rebuild를 고려
  • 인덱스 분할에 의한 경합이 현저히 높을 때
  • 자주 사용되는 인덱스 스캔 효율을 높이고자 할 때, 특히 NL 조인에서 반복 액세스되는 인덱스 높이가 증가했을 때
  • 대량의 delete 작업을 수행한 이후 다시 레코드가 입력되기까지 오랜 기간이 소요될 때
  • 총 레코드 수가 일정한대도 인덱스가 계속 커질 때