SEQUENTIAL ACCESS 는 레코드를 논리적 또는 물리적 순서에 따라 차례로 읽어나가는 방식을 말합니다. RADOM ACCESS는 레코드간 논리적 물리적 순서를 따르지 않고 한 건을 읽기 위해 한 블록씩 접근하는 방식입니다. 결과적으로 I/O 튜닝의 핵심 원리는 다음과 같습니다.
- 랜덤 엑세스 발생량을 줄이기.
- 시퀀셜 엑세스에 의한 선택 비중 높이기.
인덱스 선행 칼럼이 범위조건일때의 비효율
인덱스 구성 칼럼이 조건절에서 모두 등치 조건으로 사용되면 리프 블록 스캔을 통해 읽은 레코드는 모두 테이블 액세스로 이어집니다. 읽고 버리는 레코드가 하나도 없으므로 인덱스 스캔 효율이 100% 입니다. 인덱스 칼럼 중 일부가 등치 조건이 아니거나 조건절에서 생략되더라도 뒤쪽 칼럼일 경우 비효율은 발생하지 않습니다. 다음과 같은 인덱스와 조건절의 구성은 인덱스 스캔 비효율이 없습니다.
-- 인덱스 구성 : [아파트시세코드 + 평형 + 평형타입 + 인터넷매물]
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 아파트시세코드 =
AND 평형 =
AND 평형타입 =
AND 인터넷매물 BETWEEN '1' AND '2'
ORDER BY 입력일 DESC
인터넷 매물이 between 조건이지만 선행 칼럼들이 모두 = 조건이므로 전혀 비효율 없이 2건을 빠르게 찾습니다. 인덱스 선행 칼럼이 모두 '=' 조건일 때, 필요한 범위만 스캔하고 멈출 수 있는 이유는 조건을 만족하는 레코드가 모두 한데 모여 있기 때문입니다.
인덱스 구성을 인터넷매물 + 아파트시세코드 + 평형 + 평형타입 순으로 바꾸고 나서 같은 SQL을 수행해보면 인덱스 스캔 범위가 넓어집니다. 인덱스 선두 칼럼인 인터넷매물에 Between 연산자를 사용하면 등차비교 조건을 만족하는 레코드들이 인터넷매물값별로 흩어집니다. 따라서 조건을 만족하지 않는 레코드까지 스캔하고서 버려야 하는 비효울이 생깁니다.
범위조건을 IN-LIST로 전환
범위 조건을 BETWEEN IN-LIST로 전환하는 방식은 인덱스 수평적 탐색의 범위를 줄이기 위해 수직저 탐색을 더하는 방식입니다. 인덱스 선두 컬럼의 between 조건을 IN-LIST 조건으로 바꿀 때 주의할 점은, IN-LIST의 수가 많지 않아야 합니다. 인덱스 수직 탐색이 여러번 발생하기 때문입니다. 인덱스 높이에 따라 수평적 탐색보다, 브랜치 블록 탐색을 반복하는 수직적 탐색의 비효율이 더 커질 수 있습니다.
범위조건을 2개 이상 사용할 때의 비효율
범위검색 조건을 2개 이상 사용하면, 첫 번째 조건이 인덱스 스캔의 범위를 결정하고, 두 번째 범위검색조건은 인덱스 필터 조건 역할만을 하기 때문에 스캔 성능이 나빠질 수 있습니다.
SELECT 고객 ID, 상품명, 지역
FROM 가입상품
WHERE 회사 = :com
AND 지역 like :reg || '%'
AND 상품명 like :prod || '%'
위의 쿼리에서 두 개의 범위 조건을 사용하는 것 보다 쿼리를 2개로 분리해서 사용하는 것이 좋습니다.
-- 지역 값이 입력되지 않는 경우
SELECT 고객 ID, 상품명, 지역
FROM 가입상품
WHERE 회사 = :com
AND 상품명 like :prod || '%'
-- 지역 값이 입력되는 경우
SELECT 고객 ID, 상품명, 지역
FROM 가입상품
WHERE 회사 = :com
AND 지역 = :reg
AND 상품명 like :prod || '%'
또는 아래처럼 UNION ALL을 이용하는 방법도 있습니다.
SELECT 고객 ID, 상품명, 지역
FROM 가입상품
WHERE :REG IS NULL
WHERE 회사 = :com
AND 상품명 like :prod || '%'
UNION ALL
SELECT 고객 ID, 상품명, 지역
FROM 가입상품
WHERE :REG IS NOT NULL
AND 회사 = :com
AND 지역 = :reg
AND 상품명 like :prod || '%'인덱스 스캔 비효율 튜닝
인데스 스캔으로 얻은 인덱스 레코드의 수가 결과집합의 레코드 수에 비해 많은 경우
조건비교 컬럼이 인덱스 선두에 위치 - 인덱스 필터조건으로 인한 비효율
인덱스에 포함되지 않은 컬럼이 조건절로 사용 - 테이블 필터조건으로 인한 비효율
옵션조건 사용시 주의사항
옵션조건에 OR 방식을 사용하면 등치조건 칼럼이 선두 칼럼이더라도 INDEX RANGE SCAN을 할 수 없습니다.
SELECT * FROM TRANSAC
WHERE (#CUST_DT# IS NULL OR CUST_ID = #CUST_ID#)
AND TRAN_DATE BETWEEN #DT1# AND #DT2#
옵션조건에 LIKE 조건을 사용하면 입력값이 없는 경우 거래 일자가 액세스 조건이 되는 것이 아닌 모든 고객번호(%)를 찾기위해 모든 인덱스 컬럼을 스캔합니다.
SELECT * FROM TRANSAC
WHERE #CUST_DT# LIKE #CUST_ID# || '%'
AND TRAN_DATE BETWEEN #DT1# AND #DT2#
옵션조건에 NVL 함수를 사용하면 UNION ALL 형태로 쿼리변화니 작동하기 때문에 고객ID 처럼 변별력이 좋은 칼럼에 효과적으로 사용할 수 있습니다.
SELECT * FROM TRANSAC
WHERE 고객ID = NLV(#CUST_ID#, 고객ID)
AND TRAN_DATE BETWEEN #DT1# AND #DT2#
SELECT * FROM TRANSAC
WHERE :CUST_ID IS NULL
AND TRAN_DATE BETWEEN #DT1# AND #DT2#
UNION ALL
SELECT * FROM TRANSAC
WHERE :CUST_ID IS NOT NULL
AND 고객ID = :CUST_ID
AND TRAN_DATE BETWEEN #DT1# AND #DT2
옵션조건에 CASE 문을 사용하면 UNION ALL 형태로 쿼리변환이 일어나지 않습니다.
SELECT * FROM TRANSAC
WHERE 고객ID = (CASE WHEN #CUST_ID# IS NULL THEN 고객ID ELSE #CUST_ID# END)
AND TRAN_DATE BETWEEN #DT1# AND #DT2#
