[Oracle] 오라클 인덱스 스캔효율

인덱스 스캔 효율

- Sequential access의 선택도 향상을 위한 방법

☑ [Oracle] 오라클 인덱스 스캔 방식 보러가기

[인] 인덱스 매칭도

- Sequential access의 효율은 선택도에 의해 결정 (같은 결과 건수를 내는데 얼마나 적은 레코드를 읽느냐)

- 인덱스 컬럼이 조건절에 모두 '=' 조건으로 사용될 때 선택도가 가장 높음

- Leaf Block을 scan하면서 읽은 레코드가 모두 테이블 access -> 비효율X

- 인덱스 컬럼 중 일부가 조건절에 생략되거나 '=' 조건이 아니더라도 그것이 뒤쪽 컬럼일 때 비효율X

- 인덱스 선행 컬럼이 조건절에 누락되거나 between, 부등호, like 같은 범위검색 조건이면 비효율 발생

[비:B.I.] BETWEEN 조건을 IN-LIST로 바꾸었을 때 인덱스 스캔 효율

SELECT ~~~~~~~~~~~~~~~~~~

  FROM 매물아파트매매

WHERE 인터넷매물 BETWEEN '1' AND '2'

→ WHERE 인터넷매물 IN ('1', '2')

    AND 아파트시세코드='A01011350900056'

    AND 평형 = '59'

    AND 평형타입 = 'A'

ORDER BY 입력일 DESC;

- 인덱스 스캔 과정

* BETWEEN

* IN-LIST

- IN-LIST 개수만큼 UNION ALL 브랜치가 생성되고, 각 브랜치마다 모든 컬럼을 '=' 조건으로 검색

- IN-LIST 개수만큼 수직적 탐색이 이루어지므로 IN-LIST 개수가 적을때만 유용

- 인덱스 스캔 과정에서 선택되는 레코드들이 서로 멀리 떨어져 있을 때만 유용 (스캔 레코트에 비해 선택 레코드가 소량일 때)

[스:S] Index Skip Scan을 이용한 비효율 해소

CREATE INDEX 월별고객별판매집계_IDX ON 월별고객별판매집계(판매월, 판매구분);

SELECT COUNT(*)

   FROM 월별고객별판매집계 T

WHERE 판매구분 = 'A'

    AND 판매월 BETWEEN '200801' AND '200812';

- 인덱스 선두 컬럼이 BETWEEN 조건이므로 판매구분이 'B'인 레코드까지 모두 스캔 (불필요한 I/O 발생)

▼

SELECT /*+ index_ss(T 월별고객별판매집계_IDX) */ COUNT(*)

   FROM 월별고객별판매집계 T

WHERE 판매구분 = 'A'

    AND 판매월 BETWEEN '200801' AND '200812';

구분	인덱스 재생성	BETWEEN	IN-LIST	SKIP SCAN
블록 I/O	281	3,090	314	300

* IN-LIST : ROOT부터 읽음

* SKIP-SCAN : 브랜치 블록 버퍼를 Pinning 한 채로 리프 블록을 읽고 다시 블랜치 블록으로 돌아와 다음 리프 블록으로 점프 

[범] 범위검색 조건을 남용할 때 발생하는 비효율

SELECT ~~~~~~~~~~~~~~~~~~

  FROM 가입상품

WHERE 회사 = :com

    AND 지역 LIKE :reg || '%'

    AND 상품명 LIKE :prod || '%';

-- INDEX : [회사 + 지역 + 상품명]

-- 지역은 필수 입력 사항이 아님

-- 상품명은 LIKE 검색 가능해야 함

▼

SELECT ~~~~~~~~~~~~~~~~~~

  FROM 가입상품

WHERE :reg IS NULL

    AND 회사 = :com

    AND 상품명 LIKE :prod || '%'

UNION ALL

SELECT ~~~~~~~~~~~~~~~~~~

  FROM 가입상품

WHERE :reg IS NOT NULL

    AND 회사 = :com

    AND 지역 = :reg

    AND 상품명 LIKE :prod || '%'

[같] 같은 컬럼에 두 개의 범위검색 조건 사용 시 주의 사항

SELECT *

   FROM (

SELECT ROWNUM RNUM, ~~~~~~~~~~~~~~~~~~

  FROM (

SELECT ~~~~~~~~~~~~~~~~~~

   FROM 도서

WHERE 도서명 LIKE :book_nm || '%'

ORDER BY 도서명

)

WHERE ROWNUM <= 100

) 

WHERE RNUM >= 91;    -- 10 페이지만 출력

-- 뒤로 갈수록 늦어지는 패턴 (앞의 불필요한 레코드를 읽고 버리는 비효율 발생)

▼

SELECT *

   FROM (

SELECT /*+ index (도서 도서명_idx) */

ROWID RID, ~~~~~~~~~~~~~~~~~~

  FROM 도서

WHERE 도서명 LIKE :book_nm || '%'

    AND 도서명 = :last_book_nm        -- 이전 페이지에서 출력된 마지막 도서명

    AND ROWID > :last_rid                -- 이전 페이지에서 출력된 마지막 도서의 ROWID

UNION ALL

SELECT /*+ index (도서 도서명_idx) */

ROWID RID, ~~~~~~~~~~~~~~~~~~

   FROM 도서

WHERE 도서명 LIKE :book_nm || '%'

    AND 도서명 > :last_book_nm    -- 이전 페이지에서 출력된 마지막 도서명

)

WHERE ROWNUM <= 10; 

-- LIKE 조건이 driving이 되어 last_book_nm보다 적은 도서명까지 모두 스캔하는 비효율 발생

-- 튜닝 방안: WHERE TRIM(도서명) LIKE :book_nm|| '%'   

                  AND 도서명 > :last_book_nm

-- 좌변 가공으로 LIKE 조건을 인위적으로 filter 조건으로 만들어 버림

[이:e] Between과 Like 스캔 범위 비교

- Like보다 Between이 성능적으로 더 좋음

< 쿼리 1 >

SELECT COUNT(*)

  FROM 월별고객판매집계 T

WHERE 판매월 BETWEEN '200901' AND '200902'

    AND 판매구분 = 'A';

< 쿼리 2 >

SELECT COUNT(*)

  FROM 월별고객판매집계 T

WHERE 판매월 LIKE '2009%'

    AND 판매구분 = 'A';

< 쿼리 3>

SELECT COUNT(*)

  FROM 월별고객판매집계 T

WHERE 판매월 >= '200901' 

    AND 판매월 < '200903'

    AND 판매구분 = 'A';

< 쿼리 4 >

SELECT COUNT(*)

  FROM 월별고객판매집계 T

WHERE 판매월 BETWEEN '200901' AND '200902'

    AND 판매구분 = 'B';

< 쿼리 5 >

SELECT COUNT(*)

  FROM 월별고객판매집계 T

WHERE 판매월 LIKE '2009%'

    AND 판매구분 = 'B';

< 쿼리 6> 

SELECT COUNT(*)

  FROM 월별고객판매집계 T

WHERE 판매월 BETWEEN '200900' AND '200902'

    AND 판매구분 = 'B';

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

- 최근 데이터를 주로 조회 : Index [종료일 + 시작일]

- 과거 데이터를 주로 조회 : Index [시작일 + 종료일]

- Index [시작일 + 종료일] 구성일 때 최근 시점 조회

SELECT /*+ index_desc(A idx_x01) */ *

   FROM 고객별연체금액 A

WHERE 고객번호 = '123'

    AND '20170131' BETWEEN 시작일 AND  종료일

    AND ROWNUM <= 1;