SQL 튜닝이론 - 인덱스 탐색의 비효율을 유발하는 범위검색조건절

SQL 튜닝이론 - 인덱스 탐색의 비효율을 유발하는 범위검색조건절

 

운영환경에서 테이블을 IOT, 클러스터, 파티션 테이블변경하는 것은 현실적으로 많은 제약사항이 있습니다. 시스템 영향도가 크기 때문입니다. 반면 인덱스의 스캔 방식을 효율화하기 위한 작업은 비교적 쉽게 적용할 수 있습니다. 최적의 인덱스를 설계하는 원리도 인덱스 스켄 효율화에서 비롯됩니다.

인덱스 탐색방법

 

루트블록에 C1,C2 칼럼의 값이 각각 (A,3) , (B,3), (C,2)인 세 개의 레코드가 있습니다. 각 레코드에는 하위 블록의 블록 주소가 있습니다. 레코드에 있는 주소의 블록에는 자신의 키값 보다 같거나 큰 값의 레코드가 저장돼있습니다. 예를 들어 (B, 3) 레코드의 주소에 있는 블록에는 C1 = B이고 C2 = 3인 레코드보다 값이 크거나 같은 레코드만 있습니다. 루트블록의 맨 왼쪽 레코드는 키 값이 없는 LMC 레코드입니다. LMC에 있는 주소의 블록에는 상위블록의 첫 번째 레코드(LMC 레코드 제외) 보다 작거나 작은 값이 있는 레코드들만 저장돼 있습니다.

 

-- 조건절 1
-- 수직적 탐색을 통해 C1 컬럼이 B인 첫 번째 레코드를 찾고 C1 이 C인 레코드를 만나면 탐색을 멈춥니다.
-- 수직적 탐색 중 C1 = 'B'인 레코드를 찾으면 레코드가 가리키는 리프블록으로 바로 가는것이 아니라 직전 레코드(C1 = 'A')의 리프블록으로 내려갑니다.
-- 직전 레코드가 가리키는 블록에 C1이 B이고 C2가 3보다 작은 레코드가 있을 수 있기 때문입니다. 즉 C1 = 'B' 인 레코드는 조건을 만족하지만 수평스캔을 위한 시작점은 아닙니다.

WHERE C1 = 'B'

-- 조건절 2
-- 수직적 탐색을 통해 C1 = 'B' 이고 C2 = 3 인 첫 번째 레코드를 찾습니다. C2 = 4인 레코드를 만나는 순간 스캔을 멈춥니다.
-- C1 = 'B' 이고 C2 = 3 인 레코드가 가리키는 블록으로 바로 이동하는 것이 아니라 직전 레코드가 가리키는 블록으로 이동해 조건을 만족하는 스캔 시작 레코드를 찾습니다.

WHERE C1 = 'B'
AND C2 = 3

-- 조건절 3
-- 수직적 탐색을 통해 C='B' 이고 C2 >= 3인 첫 번째 레코드를 찾고 C1 = 'C' 인 레코드를 만날때 까지 스캔하다 멈춥니다. 
-- C2 >= 3 조건절로 인해 스캔 시작점 C2 = 3인 첫 번째 레코드로 한정됩니다. 
-- 범위검색 조건이지만 수직적 탐색의 과정에 사용돼 스캔 시작점을 결정하는 요소로 작용했습니다.

WHERE C1 = 'B'
AND C2 >= 3

-- 조건절 4
-- 수직적 탐색을 통해 C1 = 'B'인 첫 번째 레코드를 찾고 C2 > 3 인 레코드를 만나면 스캔을 멈춥니다.
-- C2 >= 3 조건절은 스캔 시작점을 결정하는데 역할을 하지 못했지만 C2 = 3인 마지막 레코드로 스캔 중단점을 결정하는데 영향을 줍니다.

WHERE C1 = 'B'
AND C2 <= 3

-- 조건절 5
-- 수직적 탐색을 위해 C1 = B, C2 >= 2 인 시작점을 찾고 C2 > 3인 지점에서 스캔을 멈춥니다.
-- 두 조건절 모두 스캔의 양을 결정하는데 역할을 합니다.

WHERE C1 = 'B'
AND C2 BETWEEN 2 AND 3

-- 조건절 6
-- 수직적 탐색을 위해 C1 >= 'A' 이고 C2 >= 2 인 시작점을 찾고 C1 = 'C' 이고 C2 = 3 인 지점을 찾으면 스캔을 멈춥니다.
- C1 = B 인 모든 레코드를 탐색하므로 C2 조건은 C1 이 B일 때 인덱스 스캔량에 영향이 없습니다.

WHERE C1 = BETWEEN 'A' AND 'C'
AND C2 BETWEEN 2 AND 3

 

인덱스와 조건절의 구성에 따라 쿼리 실행 결과 같더라도 인덱스 리프블록에서 스캔하는 레코드의 수가 달라집니다. 인덱스의 구성과 조건절의 구성에 따라 인덱스 스캔의 효율성이 달라지기 때문입니다. 인덱스의 선행컬럼이 조건절에 없거나 동등조건이 아니면 인덱스 스캔 과정에서 비효율이 발생할 수밖에 없습니다.

 

인덱스 스캔 효율성 측정

 

SQL 실행 후 발생하는 트래이스를 분석하면 인덱스의 효율성을 측정할 수 있습니다.

TABLE ACCESS BY INDEX ROWID BIG_TABLE (CR=7471 PR=1466 PW=0 TIME=22167 US)
  INDEX RANGE SCAN BIG_TABLE_IDX(CR=7463 PR=1466 PW=0 TIME=22328 US)

 

 

특정 sql은 실행 후 10개의 레코드를 조회하고 위와 같은 트레이스를 남겼습니다. 쿼리 수행과정에서 7463 번의 인덱스 블록 I/O가 발생했습니다. 인덱스 리프블록 하나당 평균 500개의 레코드가 담긴다고 가정하면 10개의 레코드를 조회하기 위해 약 40만 개의 인데스 레코드를 읽은 셈입니다. 큰 비효율이 있습니다.

 

인덱스 엑세스 조건과 필터 조건

인덱스 엑세스 조건은 인덱스의 스캔 범위를 결정하는 조건입니다.  인덱스 스캔의 시작점과 끝점을 결정하는데 영향을 주는 조건입니다. 테이블 엑세스 단계 이후 처리되는 조건절은 모두 필터조건입니다. 테이블 렌덤 액세스 이후 선택된 테이블 레코드를 결과 집합에 포함시킬지 말지 결정합니다.

 

비교 연산자의 사용과 컬럼 순서에 따른 군집성

 

인덱스는 같은 값을 갖는 레코드끼리 군집해 있습니다. = 연산자를 사용하면 조건에 정확히 맞는 레코드가 모여있기 때문에 빠르게 조회할 수 있습니다. 반면 조건절에서 칼럼을 누락하거나 = 조건이 아닌 연산자로 조회하면 조건을 만족하는 레코드가 서로 흩어진 상태가 됩니다.

 

- idx = [C1 + C2 + C3+ C4]
-- between 범위 조건까지의 데이터는 모두 모여있지만 이후 조건까지 만족하는 데이터는 흩어지게 됩니다.
where C1 = 1
AND C2 = 'A'
AND C3 between '가' and '다'
AND C4 = 'a'

 

인덱스의 선행컬럼이 모두 = 조건인 상태에서 첫 번째 나타나는 범위검색 조건까지 만족하는 인덱스 레코드는 모두 연속해서 모여있습니다. 그 이후 조건을 만족하는 레코드는 모두 흩어져있습니다. 선행 칼럼이 모두 = 조건인 상태에서 첫 번째 나타나는 범위검색 조건에 사용된 인덱스 칼럼까지 인덱스 액세스 조건입니다. 이후 칼럼들은 모두 인덱스 필터 조건입니다.

 

범위검색 조건 맨 처음과 마지막 구간의 액세스 조건

 

-- idx = [C1 + C2 + C3+ C4]
where C1 Between 1 and 3
AND C2 = 'A'
AND C3 = '나'
AND C4 = 'a'

 

위 조건절에서 인덱스 액세스 조건에 해당하는 컬럼은 C1 뿐입니다. 하지만 c2, c3, c4 칼럼도 C1 = 1일 때 시작점을 결정하는데 기여하고
C1 = 3일때 끝점을 결정하는데 기여함으로써 인덱스 스캔량을 줄이는데 역할을 합니다. 오라클의 실행계획의 상단과 하단에 액세스 조건과 필터 조건을 정리해서 보여주는데 위와 같은 경우 C2, c3, c4 도 액세스 조건에 포함됩니다. 이론상 엑세스 조건과 필터 조건의 구분은 명확합니다. 하지만 현실적으로 아래 몇 가지 케이스를 제외하면 인덱스 칼럼에 대한 조건절은 대부분 액세스 조건으로 표시됩니다. 명확히 필터 조건으로 분류되는 조건절은 아래와 같습니다.

 

• 좌변 컬럼을 가공한 조건절
• 문자열 왼쪽 혹은 양쪽에 '%'를 사용한 like 조건절
• 같은 컬럼에 대한 두 개 이상의 범위 조건절 중 인덱스 액세스 조건에 포함되지 못한 조건절
• OR EXPANSION 또는 INLIST ITERATOR 로 선택되지 못한 or 또는 in 조건절