1. Index Skip Scan 활용
BETWEEN 조건을 IN-List 조건으로 변환하면 도움이 되는 상황에서 굳이 조건절을 바꾸지 않고도 Index Skip Scan을 사용하여 같은 효과를 낼 수 있다.
CREATE TABLE 월별고객별판매집계
AS
SELECT rownum 고객번호
, '2026' || 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;월별 10만개 판매 데이터를 생성했다.
SELECT COUNT(*)
FROM 월별고객별판매집계
WHERE 판매구분 = 'A'
AND 판매월 BETWEEN '202601' AND '202612'이 쿼리를 최적으로 수행하려면 '=' 조건인 판매구분이 선두컬럼에 위치하도록 인덱스를 구성해야 된다.
CREATE INDEX 월별고객별판매집계_IDX1 ON 월별고객별판매집계(판매구분, 판매월);IDX1을 사용하면 인덱스를 스캔하면서 281개의 블록 I/O가 발생했다.
CREATE INDEX 월별고객별판매집계_IDX2 ON 월별고객별판매집계(판매월, 판매구분);판매구분 = 'A'인 레코드는 각 판매월 앞쪽에 위치하며, 전체에서 8.3%(=10/120)에 불과하므로 서로 멀리 떨어지게 된다.
IDX2를 사용하면 3090개 블록I/O가 발생한다.
테이블을 전혀 방문하지 않았는데도 I/O가 많이 발생한 이유는 인덱스 선두 컬럼이 BETWEEN 조건이어서 판매구분이 'B'인 레코드가지 모두 스캔하고서 버렸기 때문이다.
다시 BETWEEN 조건을 IN-LIST로 전환해보자
WHERE 판매월 IN ('202601', '202602', '202603', '202604', '202605', '202606'
, '202607', '202608', '202609', '202610', '202611', '202612')3090개이던 블록 I/O가 314개로 감소하였다.
인덱스 브랜치 블록을 열두번 반복 탐색했지만, 리프 블록을 스캔할 때의 비효율을 제거함으로써 성능이 열 배 좋아졌다.
다시 WHERE 조건을 BETWEEN으로 수정 후, INDEX_SS(INDEX SKIP SCAN)을 사용하여 조회했을 때, 큰 비효율 없이 블록I/O가 300개로 감소했다.
선두컬럼이 BETWEEN이고, 나머지 검색 조건을 만족하는 데이터들이 서로 멀리 떨어져 있을 때 Index Skip Scan은 성능이 좋다.
IN 조건은 '='인가
인덱스 구성 1. [상품ID + 고객번호]
인덱스 구성 2. [고객번호 + 상품ID]
두 구성이 차이가 있는가?
SELECT *
FROM 고객별가입상품
WHERE 고객번호 = CustSeq
AND 상품ID IN ('CNC102', 'CNC103', 'CNC104')고객별가입상품 테이블에서 고객번호의 평균 카디널리티는 3이라고 가정한다.
만약 인덱스 구성이 상품ID + 고객번호로 구성돼 있다면, 상품은 고객번호 순으로 정렬된 상태이다.
그렇다면 상품ID 조건절이 IN-List Iterator 방식으로 풀리는 것이 효과적이다.
고객번호 = 1 조건을 만족하는 레코드가 서로 멀리 떨어져 있기 때문이다.
SELECT *
FROM 고객별가입상품
WHERE 고객번호 = 1
AND 상품ID = 'CNC102'
UNION ALL
SELECT *
FROM 고객별가입상품
WHERE 고객번호 = 1
AND 상품ID = 'CNC103'
UNION ALL
SELECT *
FROM 고객별가입상품
WHERE 고객번호 = 1
AND 상품ID = 'CNC104'위 코드는 수직적 탐색 3번으로 총 아홉개 블록을 읽는다.
(상품ID를 기준으로 수직적 탐색 3번, 고객번호 1을 찾는 과정 3번으로 3X3)
상품ID가 인덱스 선두 컬럼인 상황에서 IN-LIST ITERATOR 방식으로 풀지 않으면, 상품ID는 필터 조건이므로 테이블 전체 또는 인덱스 전체를 스캔하면서 필터링해야 된다.
이번에는 인덱스 구성 2. [고객번호 + 상품ID]이다.
이러한 인덱스 구성에서 IN-LIST ITERATOR 방식을 사용하면 비효율적이다. 고객번호를 기준으로 상품ID들이 모여있어서 수직적 탐색을 1번(또는 2번)만 하면 되는데 3번의 수직적 탐색을 해야되기 때문이다.
고객번호 1이 한 블록에 모여 있다면, 블록I/O는 수직적 탐색 과정을 포함해 총 3번만 발생한다.
그렇기 때문에 IN조건은 '='이 아니다.
IN조건이 '='이 되려면 IN-LIST ITERATOR 방식으로 풀려야만 한다. 그렇지 않으면 IN 조건은 필터 조건이다.
BETWEEN과 LIKE 스캔 범위 비교
범위 조건을 사용할 때 BETWEEN보다 LIKE를 많이 사용하게 된다.
결론부터 말하면 LIKE보다는 BETWEEN을 사용하는게 낫다.
인덱스 구성 [판매월 + 판매구분]
조건절 1.
WHERE 판매월 BETWEEN '202601' AND '202612'
AND 판매구분 = 'B'
조건절 2.
WHERE 판매월 LIKE '2026%'
AND 판매구분 = 'B'위 코드에서 조건절 1은 판매월 = '202601'이고 판매구분 'B'인 첫 번째 레코드에서 스캔을 시작한다.
반면, 조건절 2는 판매월 = '202601'인 첫 번째 레코드에서 스캔을 시작한다.
혹시라도 202600이 저장돼 있다면 해당 레코드도 읽어야 되기 때문에 판매구분 = 'B'인 지점으로 바로 내려갈 수 없다.
또한 '202613' 값이 저장돼 있다면 그 값도 읽어야 하므로 중간에 멈출 수 없다.
범위검색 조건을 남용할 때의 비효율
가입상품 테이블에 인덱스 구성을 [회사코드 + 지역코드 + 상품명] 이렇게 구성하였다.
이때 사용자가 데이터 조회를 위해 회사코드 + 지역코드 + 상품명을 입력할 수도 있고, 회사코드 + 상품명만을 이용해서 데이터 조회할 수도 있다.
쿼리 1)
SELET *
FROM 가입상품
WHERE 회사코드 = CompanySeq
AND 지역코드 = RegionCd
AND 상품명 LIKE ProdNm + '%'
쿼리 2)
SELCT *
FROM 가입상품
WHERE 회사코드 = CompanySeq
AND 상품명 LIKE ProdNm + '%'인덱스 중간 컬럼에 대한 조건이 없는 쿼리 2는 어쩔 수 없이 넓은 범위를 스캔하지만,
쿼리 1에서는 세 컬럼 모두 액세스 조건이므로 아주 적은 범위만 스캔하고 빠르게 결과를 출력할 수 있다.
그런데 만약 두 가지 상황을 하나의 SQL로 처리한다면 어떻게 변할까?
SELET *
FROM 가입상품
WHERE 회사코드 = CompanySeq
AND 지역코드 = RegionCd + '%'
AND 상품명 LIKE ProdNm + '%'해당 SQL 쿼리를 사용한다면 지역코드를 입력하지 않은 경우는 쿼리2와 동일한 결과를 얻겠지만, 지역코드가 입력된 상황에서는 RegionCd가 '02'인 경우에도 '021', '022'와 같은 데이터가 있는 것을 염두해두고 인덱스 스캔 범위가 늘어날 것이다. 앞서 액세스 조건이던 상품명이 필터 조건으로 바뀌면서 생긴 변화다.
물론 해당 쿼리를 사용하면 SQL 하나로 모든 상황에 대한 처리를 할 수 있지만, 코딩을 쉽게 하려고 이처럼 인덱스 컬럼에 범위검색을 남용하면 인덱스 스캔 비효율이 생긴다.
