[SQL] 인덱스(Index) - (2) 인덱스 기본 사용법 - 2

이전 글 : https://sewonzzang.tistory.com/37

[SQL] 인덱스(Index) - (2) 인덱스 기본 사용법 - 1

---

3. 더 중요한 인덱스 사용 조건

 

조건절에서 인덱스 컬럼을 가공하면 인덱스를 정상적으로 사용할 수 없다는 사실을 이해했을 것 입니다.

그런데 인덱스를 사용하는데 있어 더 중요한 선행조건을 알아 볼 것 입니다.

 

인덱스를 아래처럼 [소속팀 + 사원명 + 연령] 순으로 구성했습니다.

아래 조건절에 대해 인덱스를 정상적으로 Range Scan 할 수 있을까요?

select 사원번호, 소속팀, 연령, 입사일자, 전화번호
from 사원
where 사원명 = '홍길동'

인덱스를 [소속팀 + 사원명 + 연령] 순으로 구성한다는 의미를 잘 새겨봐야 합니다.

'데이터를 소속팀 순으로 정렬하고, 소속팀이 같으면 사원명 순으로 정렬하고, 사원명까지 같으면 연령순으로 정렬한다."는 의미입니다.

그렇가면 이름이 같더라도 소속팀이 다르면 서로 멀리 떨어지게 됩니다.

사원명이 홍길동인 조건을 만족하는 데이터는 리프 블록 전 구간에 흩어져버립니다.

 

이 조건으로 검색하면, 인덱스 스캔 시작점을 찾을 수 없고, 어디서 멈춰야 할지도 알 수 없습니다. 인덱스 리프 블록을 처음부터 끝까지 모두 스캔해야 합니다.

인덱스를 Range Scan 하기 위한 가장 첫 번째 조건은 인덱스 선두 컬럼이 조건절에 있어야 한다는 사실입니다. 가공하지 않은 상태로 말입니다.

 

아래 SQL은 인덱스 컬럼을 가공했는데, 어떻게 인덱스를 Range Scan 할 수 있습니까?

select * from TXA1234
where 기준연도 = :stdr_year
and substr(과세구분코드, 1, 4) = :txtn_dcd
and 보고회차 = :rpt_tmrd
and 실명확인번호 = :rnm_cnfm_no

 

인덱스를 Range Scan 하려면 인덱스 선두 컬럼이 가공되지 않은 상태로 조건절에 있어야 합니다.

반대로 말해서, 인덱스 선두 컬럼이 가공되지 않은 상태로 조건절에 있으면 인덱스 Range Scan은 무조건 가능합니다.

 

위 질문 사례로 말하면, 인덱스 선두 컬럼인 '기준연도'를 조건절에서 가공하지 않았으므로 인덱스 Range Scan이 가능합니다. 문제는, 인덱스를 Range Scan 한다고 해서 항상 성능이 좋은건 아니라는 사실입니다.\

 

인덱스를 잘 타니까 튜닝 끝?

 

우리가 흔히 말하는 '인덱스를 탄다'는 표현은 '인덱스를 Range Scan한다' 와 같은 의미입니다.

인덱스를 잘 타니까 성능에 문제가 없을까요?  

 

Execution Plan
---------------------------------------------------------
0	SELECT STATEMENT Optimizer = ALL_ROWS
1	0	TABLE ACEESS (BY INDEX ROWID) OF '주문상품' (TABLE)
2	1	  INDEX (RANGE SCAN) OF '주문상품_N1' (INDEX)

주문상품_N1 인덱스는 [주문일자 + 상품번호] 순으로 구성됐고, 이 테이블에 쌓이는 데이터량은 하루 평균 100만건이라고 가정합니다.

 

 

아래 조건절은 인덱스 선두 컬럼인 주문일자가 조건절에 있고, 가공하지 않은 상태이므로 인덱스를 Range Scan 하는 데 문제가 없습니다.

스캔 시작점을 찾아 스캔하다가 중간데 멈출 수 있습니다. 그런 의미에서 인덱스를 잘 탄다고 할 수 있습니다.

그런데 인덱스를 정말 잘 타는지는 인덱스 리프 블로에서 스캔하는 양을 따져봐야 알 수 있습니다.

SELECT *
FROM 주문상품
WHERE 주문일자 = :ord_dt
AND 상품번호 LIKE '%PING%';

SELECT *
FROM 주문상품
WHERE 주문일자 = :ord_dt
AND SUBSTR(상품번호, 1, 4) = 'PING';

 

위 SQL에서 상품번호는 스캔 범위를 줄이는 데 전혀 역할을 하지 못합니다.

첫 번째 SQL은 중간 값 검색이기 때문이고, 두 번째 SQL은 컬럼을 가공했기 때문입니다.

따라서 위 조건적을 처리할 때 인덱스에서 스캔하는 데이터량은 주문일자 조건을 만족하는 100만 건 입니다. 이를 두고 인덱스를 잘 탄다고 말할 수 있을까요?

글 작성후에 링크를 걸어두겠습니다.(인덱스 스캔 효율화)

 

4. 인덱스를 이용한 소트 연산 생략

 

인덱스는 테이블과 다르게 정렬돼 있습니다.

인덱스가 정렬돼 있기 때문에 Range Scan이 가능하고, 지금부터 설명하는 소트 연산 생략 효과도 얻게 될 것 입니다.

 

PK를 아래 처럼 [장비번호 + 변경일자 + 변경순번] 순으로 구성한 상태변경이력 테이블이 있다고 가정해 보겠습니다.

장비번호	변경일자	변경순번
...	...	...
B	20180505	031583
C	20180316	000001
C	20180316	000002
C	....	131576
C	20180428	000001

PK인덱스는 장비번호, 변경일자가 같은 레코드는 변경순번 순으로 정렬돼 있습니다.

아래와 같이 장비번호와 변경일자를 모두 '='조건으로 검색할 때 PK 인덱스를 사용하면 결과집합은 변경순번 순으로 출력됩니다.

 

옵티마이저는 이런 속성을 활용해 SQL에 ORDER BY가 있어도 정렬 연산을 따로 수행하지 않습니다.

PK 인덱스를 스캔하면서 출력한 결과집합은 어차피 변경순번 순으로 정렬되기 때문입니다. 아래 실행계획에 SORT ORDER BY 연산이 없음을 확인 할 수 있습니다. 

 

만약 정렬 연산을 생략할 수 있게 인덱스가 구성돼 있지 않다면, 아래와 같이 SORT ORDER BY 연산 단계가 추가됩니다.

SELECT *
FROM 상태변경이력
WHERE 장비번호 = 'C'
AND 변경일자 = '20180316'
ORDER BY 변경순번

Execution Plan
--------------------
SELECT STATEMENT Optimizer = ALL_ROWS
TABLE ACCESS (BY INDEX ROWID) OF '상태변경이력'
INDEX (RANGE SCAN) OF '상태변경이력_PK'


Execution Plan (SORT)
--------------------
SELECT STATEMENT Optimizer = ALL_ROWS
SORT (ORDER BY) 
TABLE ACCESS (BY INDEX ROWID) OF '상태변경이력'
INDEX (RANGE SCAN) OF '상태변경이력_PK'

 

오름차순(Asc) 정렬일 때는 조건을 만족하는 가장 작은 값을 찾아 좌측으로 수직적 탐색한 후 우측으로 수평적 탐색을 합니다.

내림차순(Desc) 정렬일 때는 조건을 만족하는 가장 큰 값을 찾아 우측으로 수직적 탐색한 후 좌측으로 수평적 탐색을 합니다.

 

SORT ORDER BY 가 없으면서 INDEX RANGE SCAN 단위에 DESCENDING이라고 표시된 부분이 생길 것 입니다.

 

인덱스로 소트 연산을 생략함으로써 성능을 높이는 구체적인 튜닝 기법은 다음에 자세히 다루겠습니다

 

5. ORDER BY 절에서 컬럼 가공

 

모든 SQL 튜닝 책이 다루는 "인덱스 컬럼을 가공하면 인덱스를 정상적으로 사용할 수 없다"에서 말하는 '인덱스 컬럼'은 대게 조건절에서 사용한 컬럼을 말합니다. 그런데 조건절이 아닌 ORDER BY 또는 SELECT-LIST에서 컬럼을 가공함으로 인해 인덱스를 정상적으로 사용할 수 없는 경우도 종종 있습니다.

 

앞의 표에서 PK 인덱스를 [장비번호 + 변경일자 + 변경순번] 순으로 구성했다면, 아래 SQL도 정렬 연산을 생략할 수 있습니다.

수직적 탐색을 통해 장비번호가 'C'인 첫 번째 레코드를 찾아 인덱스 리프 블록을 스캔하면, 자동으로 [변경일자 + 변경순번] 순으로 정렬되기 때문입니다.

SELECT *
FROM 상태변경이력
WHERE 장비번호 = 'C'
ORDER BY 변경일자, 변경순번

 

다른 예시를 들어보겠습니다.

주문_PK 인덱스는 [주문일자 + 주문번호] 순으로 구성돼 있습니다. 아래 SQL 에 주문_PK 인덱스를 사용하면 선두 컬럼인 주문일자가 '=' 조건이므로 데이터가 주문번호 순으로 출력됩니다. ORDER BY 절이 있어도 정렬 연산을 생략할 수 있는 상태입니다. 그런데도 아래 실행계획에 SORT ORDER BY 연산이 나타난 이유는 무엇일까요?

 

SELECT *
FROM (
	SELECT TO_CHAR(A.주문번호, 'FM00000') AS 주문번호, A.업체번호, A.주문금액
    FROM 주문 A
    WHERE A.주문일자 = :dt
    AND A.주문번호 > NVL(:next_ord_no, 0)
    ORDER BY 주문번호
    )
WHERE ROWNUM <= 30


>>>> SORT ORDER BY STOPKEY

 

ORDER BY 절에 기술한 '주문번호'는 순수한 주문번호가 아니라 TO_CHAR 함수로 가공한 주문번호를 가리키기 때문입니다. 참고로, TO_CHAR 함수에 'FM00000' 옵션을 사용하면, 첫 번째 인자에 입력한 숫자 값을 '0'으로 시작하는 여섯자리 문자 값으로 변환해 줍니다. 예를 들어, 숫자 1234를 입력하면 , 문자 '001234'으로 변환해 줍니다.

원인을 알았으니 해결방법은 간단합니다. ORDER BY 절 주문번호에 A(주문 테이블 Alias)를 붙여주기만 하면 됩니다.

 

참고로, 애초에 발견한 SQL의 ORDER BY 절에는 '주문번호'가 아니라 '1'이라고 적혀 있었습니다. '1'은 SELECT-LIST에 나열된 첫 번째 컬럼을 의미합니다.

 

---

다음 글: https://sewonzzang.tistory.com/39

[SQL] 인덱스(Index) - (2) 인덱스 기본 사용법 - 3