3회차 인덱스 튜닝

개발 독서 스터디/친절한 SQL 튜닝

3회차 인덱스 튜닝 - 2

seoyyyy 2025. 2. 8. 15:13

3. 인덱스 스캔 효율화

3.1 인덱스 탐색

인덱스 스캔 효율화 튜닝을 이해하기 위해 여러 조건절에 따른 인덱스 탐색 과정을 분석해보자

위 그림은 루트 블록에 C1, C2 컬럼인 레코드 세개가 있고, 각 레코드는 하위노드를 가리키는 블록 주소를 갖는다.

< 조건절 1 >
WHERE C1 = 'B'

조건절 1은 수직적 탐색을 통해 C1 = 'B' 인 첫번째 레코드를 찾고 'C'를 만나는 순간 스캔을 멈춘다.
주의할 점은 루트 블록 스캔 과정에서 C1 = 'B'인 레코드를 찾을 때 그것이 가리키는 리프 블록 3으로 내려가면 안되고 그 직전 레코드가 가리키는 리프블록 2로 내려가서 스캔 시작점을 찾는다.

<조건절 2>
WHERE C1 = 'B'
AND C2 = 3

조건절 2도 수직적 탐색을 통해 C1 = 'B' 이고 C2 = 3인 첫번 째 레코드를 찾고, C1 = 'B'인 레코드 중 C2 = 4인 레코드를 만나는 순간 스캔을 멈춘다. C1, C2 조건절 모두 스캔 시작과 끝 지점을 결정하여 스캔량을 줄이는데 중요한 역할을 했다.

<조건절 3>
WHERE C1 = 'B'
AND C2 >= 3

조건절 3은 수직적 탐색을 통해 C1 = 'B'이고 C2 >=3 인 첫 번째 레코드를 찾고, C1 = 'C'인 레코드를 만날 때까지 스캔하다가 멈춘다.
C2 >= 3 조건절이 스캔을 멈추는데 역할은 못하지만 이 조건절로 조건절1과 스캔 시작점은 달라졌다.
부등호 조건이지만 수직적 탐색과정에 사용됨으로써 스캔량을 줄이는데 역할을 했다.

<조건절 4>
WHERE C1 = 'B'
AND C2 <= 3

조건절 4는 수직적 탐색을 통해 C1 ='B'인 첫번째 레코드를 찾고, 거기서부터 스캔하다 C2 > 3 인 첫 번째 레코드를 만나는 순간 스캔을 멈춘다.
C2 <= 3 조건절은 수직적 탐색과정에 쓰이지 않아 스캔 시작점을 결정하는데 역할을 하지는 못하지만 스캔을 멈추는데는 중요한 역할을 했다. 즉 스캔량을 줄였다.

<조건절 5>
WHERE C1 = 'B'
AND C2 BETWEEN 2 AND 3

조건절 5는 수직적 탐색을 통해 C1 = 'B'이고 C2 >= 2 인 첫 번째 레코드를 찾고, C2 > 3 인 첫 번째 레코드를 만나는 순간 스캔을 멈춘다.
이번엔 C1과 C2 조건절 모두 스캔 시작, 끝 지점을 결정하는데 중요한 역할을 해 스캔량을 줄였다.

<조건절6>
WHERE C1 BETWEEN 'A' AND 'C'
AND C2 BETWEEN 2 AND 3

조건절 6은 수직적 탐색을 통해 C1 >= 'A' 이고 C2 >= 2 인 첫 번째 레코드에서 스캔을 시작, C1 = 'C'이고 C2 = 3인 레코드보다 값이 큰 레코드를 만나는 순간 스캔을 멈춘다.
C1 조건은 스캔 시작과 끝 지점을 결정하는데 중요한 역할을 했지만 C2는 그렇지 못했다.
즉 C2는 스캔량을 줄이는데 거의 역할을 하지 못했다.

3.2 인덱스 스캔 효율성

이번에도 인덱스 스캔 효율성을 알아보기 위해 두가지 조건절에 따른 인덱스 스캔 과정을 분석해보자

위 그림과 같은 용어 사전 테이블이 있고, 인덱스가 C1, C2, C3, C4 컬럼 순으로 생성되어있다.

<조건절 1>
WHERE C1 = '성'
AND C2 = '능'
AND C3 = '검'

조건절 1은 '성능검'으로 시작하는 레코드를 검색할 때 인덱스를 수직적 탐색해 '성능검사' 레코드로 찾아간다.
거기서 스캔을 시작해 '성능계수' 까지 총 세 개 레코드를 스캔하고 멈춘다.

<조건절 2>
WHERE C1 = '성'
AND C2 = '능'
AND C4 = '선'

조건절 2는 '성능'으로 시작하고 네 번째 컬럼이 '선'인 레코드를 검색하기 위해 '성능'으로 시작하는 레코드를 모두 스캔한다.
결과는 조건절1과 똑같이 두 건이지만 인덱스 선행 컬럼(C3)이 조건절에 없기 때문에 훨씬 더 많은 인덱스 레코드를 스캔해야한다.
인덱스 선행컬럼이 조건절에 없거나 '=' 조건이 아니면 인덱스 스캔 과정에 비효율이 발생한다.

😊 선두 컬럼, 선행 컬럼
: '선두 컬럼'은 인덱스 구성상 '맨 앞쪽'에 있는 컬럼, '선행 컬럼'은 어떤 컬럼보다 '상대적으로 앞쪽에' 놓인 컬럼을 칭할 때 사용

3.3 액세스 조건과 필터 조건

인덱스를 스캔하는 단계에 처리하는 조건절은 액세스 조건과 필터 조건으로 나뉜다.

😀 인덱스 액세스 조건
- 인덱스 스캔 범위를 결정하는 조건절이다.
- 인덱스 수직적 탐색을 통해 스캔 시작점을 결정하는데 영향을 미치고, 인덱스 리프 블록을 스캔하다가 어디서 멈출지를 결정하는 데 영향을 미치는 조건절이다.
😀 인덱스 필터 조건
- 테이블로 액세스할지를 결정하는 조건절이다.
- 3.2의 조건절 1에서는 C1, C2, C3가 모두 인덱스 액세스 조건이었고 조건절2에서는 C1, C2가 인덱스 액세스 조건, C4는 인덱스 필터 조건이었다.
- 인덱스를 이용하든 테이블 Full Scan 하든, 테이블 액세스 단계에서 처리되는 조건절은 모두 필터 조건이다.
😀 테이블 필터조건
- 쿼리 수행 다음 단계로 전달하거나 최종 결과집합에 포함할지를 결정한다.

😎옵티마이저의 비용 계산 원리

비용 = 인덱스 수직적 탐색 비용 + 인덱스 수평적 탐색 비용 + 테이블 랜덤 액세스 비용

= 인덱스 루트와 브랜치 레벨에서 읽는 블록 수 +

인덱스 리프 블록을 스캔하는 과정에서 읽는 블록 수 +

테이블 액세스 과정에 읽는 블록 수

3.4 비교 연산자 종류와 컬럼 순서에 따른 군집성

- 테이블과 달리 인덱스는 같은 값 '=' 을 갖는 레코드들이 서로 군집해 있다.
- 인덱스 컬럼을 앞쪽부터 누락 없이 '=' 연산자로 조회하면 조건절을 만족하는 레코드는 모두 모여있다.
- 어느 하나를 누락하거나 '=' 조건이 아닌 연산자(ex. 범위연산, like '%' 연산)로 조회하면 조건절을 만족하는 레코드가 서로 흩어진 상태가 된다.

예를들어 어느테이블의 C1, C2, C3, C4 컬럼 순으로 인덱스를 생성하였을 때 조건절을 분석해보자

<조건절 1>
WHERE C1 = 1
AND C2 = 'A'
AND C3 = '나'
AND C4 = 'a'

조건절 1은 인덱스 구성 컬럼(C1,C2,C3,C4)를 모두 '=' 조건으로 비교하여 조건을 만족하는 레코드들이 모두 연속해서 모여있다.

<조건절 2>
WHERE C1 = 1
AND C2 = 'A'
AND C3 = '나'
AND C4 >= 'a'

조건절 2는 선행컬럼이 모두 '=' 이고 맨 마지막 컬럼만 범위 검색(부등호, BETWEEN, LIKE 도 해당)일 때도 조건을 만족하는 레코드가 서로 모여있다.

<조건절 3>
WHERE C1 = 1
AND C2 = 'A'
AND C3 BETWEEN '가' AND '다'
AND C4 = 'a'

조건절 3은 맨 마지막 컬럼이 아닌 중간 컬럼이 범위 검색 조건으로 C1부터 C3까지 세 조건을 만족하는 인덱스 레코드는 서로 모여있지만 C4 조건까지 만족하는 레코드는 흩어지게 된다.

<조건절4>
WHERE C1 = 1
AND C2 <= 'B'
AND C3 = '나'
AND C4 BETWEEN 'a' AND 'b'

조건절 4는 두번째 컬럼 C2가 범위 검색 조건으로 C1 부터 C2까지 두 조건을 만족하는 인덱스 레코드는 서로 모여 있지만 C3과 C4조건까지 만족하는 레코드는 흩어지게 된다.

➡️ 선행컬럼이 모두 '=' 조건인 상태에서 첫 번째 나타나는 범위검색 조건까지만 만족하는 인덱스 레코드는 모두 연속해서 모여 있지만, 그 이하 조건까지 만족하는 레코드는 비교 연산자 종류에 상관없이 흩어진다.

<조건절5>
WHERE C1 BETWEEN AND 3
AND C2 = 'A'
AND C3 = '나'
AND C4 = 'a'

조건절5는 선두 C1 컬럼이 범위검색 조건이면 C1 조건을 만족하는 레코드는 서로 모여있고, 나머지 조건까지 만족하는 레코드는 비교 연산자 종류에 상관없이 흩어지게 된다.

조건절 5의 실행계획을 살펴보면 다음과 같이 인덱스 액세스 조건, 필터 조건이 표시된다.

Predicate information (identified by operation id):

-------------------------------------------------------------------------------------------------------------------------------------

2 - access("C1" >= AND "C2" = 'A' AND "C3" = '나' AND "C4" = 'a' AND "C1" <= 3)

2 - filter("C2" = 'A' AND "C3" = '나' and "C4" = 'a')

	인덱스 액세스 조건	인덱스 필터 조건
조건절 1	C1, C2, C3, C4
조건절 2	C1, C2, C3, C4
조건절 3	C1, C2, C3	C4
조건절 4	C1, C2	C3, C4
조건절 5	C1	C2, C3, C4

😎 아래 몇 가지 케이스를 제외하면 첫 번째 조건이 범위검색 조건이여도 인덱스 컬럼에 대한 조건절은 모두 액세스 조건에 표시된다.
*** 제외 조건 ***
- 좌변 컬럼을 가공한 조건절
- 왼쪽 '%' 또는 양쪽 '%' 기호를 사용한 like 절
- 같은 컬럼에 대한 조건절이 두 개 이상일 때, 인덱스 액세스 조건으로 선택되지 못한 절
- OR Expansion 또는 INLIST ITERATOR로 선택되지 못한 OR 또는 IN 조건절

3.5 인덱스 선행 컬럼이 등치(=) 조건이 아닐 때 생기는 비효율

인덱스 스캔 효율성은 인덱스 컬럼을 조건절에 모두 등치(=) 조건으로 사용할 때 가장 좋다.
인덱스 컬럼 중 일부가 조건절에 없거나 등치 조건이 아니더라도, 그것이 뒤쪽 컬럼일 때는 비효율이 없지만 인덱스 선행 컬럼이 조건절에 없거나 부등호, BETWEEN, LIKE 같은 범위 검색 조건이면 인덱스를 스캔하는 단계에서 비효율이 생긴다.

예를들어 [아파트시세코드, 평형, 평형타입, 인터넷 매물] 순의 인덱스를 구성한 후 아래 SQL 을 수행해보자.

SELECT 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드
FROM 매물아파트매매
WHERE 아파트시세코드 = 'A01011350900056'
AND 평형 = '59'
AND 평형타입 = 'A'
AND 인터넷매물 BETWEEN '1' AND '3'
ORDER BY 입력일 DESC

인터넷매물이 BETWEEN 조건이지만 선행 컬럼들(아파트시세코드, 평형, 평형타입, 인터넷매물)이 모두 '=' 조건이므로 비효율 없이 조건을 만족하는 세 건을 빠르게 찾았다.
인덱스 선행 컬럼이 모두 '=' 조건일 때 필요한 범위만 스캔하고 멈출 수 있는 것은, 조건을 만족하는 레코드가 모두 한데 모여 있기 때문이다.

⭐ 만약 [인터넷매물, 아파트시세코드, 평형, 평형타입] 순으로 바꾼 후 같은 SQL 을 수행하면 인덱스 스캔 범위가 넓어지게 된다.

인덱스 선두 컬럼 인터넷매물에 BETWEEN 연산자를 사용하면 나머지 조건을 만족하는 레코드들이 인터넷매물 값 별로 뿔뿔이 흩어지기 때문이다. (비효율 발생!)

3.6 BETWEEN을 IN-List로 전환

범위검색 컬럼이 인덱스의 맨 뒤에 위치하도록 순서를 변경하면 좋지만 운영 시스템에서 인덱스 구성을 바꾸기는 쉽지 않은데 이럴 때 BETWEEN 조건을 아래와 같이 IN-LIST로 바꿔주면 큰 효과를 얻는 경우가 있다.

SELECT 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드
FROM 매물아파트매매
WHERE 인터넷매물 IN ('1', '2', '3')
AND 아파트시세코드 = 'A01011350900056'
AND 평형 = '59'
AND 평형타입 = 'A'
ORDER BY 입력일 DESC

위 그림에서 왼쪽에 화살표가 세개인 이유는 옵티마이저가 해당 In 절을 Union all 로 변환해 모든 컬럼이 '=' 조건으로 되어 결과적으로 인덱스 수직적 탐색이 세 번 발생하기 때문이다. (INLIST ITERATOR)
➡️ 실행계획

--------------------------------------------------------------------------------------
| ID  | Operation                          | Name             | Rows  | Bytes | Cost  |  
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                  |       |       |      6 |
|   1 |   INLIST ITERATOR                  |                  |       |       |        |
|   2 |     TABLE ACCESS BY INDEX ROWID    | 매물아파트매매    |    3  |    37 |      6 |
|   3 |       INDEX RANGE SCAN             | 매물아파트매매_PK |    3  |       |      5 |
--------------------------------------------------------------------------------------

😎 BTEWEEN 조건을 IN-List 로 전환할 때 주의사항
- IN-List 개수가 많으면 수직적 탐색이 많아져 BETWEEN 조건에서 리프 블록을 많이 스캔해야하는 비효율보다 IN-List 개수만큼 브랜치 블록을 반복 탐색하는 비효율이 더 클 수 있으므로 IN-List 개수가 많지 않아야 한다. 루트에서 브랜치 블록까지 Depth가 깊을 때 특히 그렇다.
- 인덱스 스캔 과정에서 선택되는 레코드들이 서로 멀리 떨어져 있을 때만 유용하다.
- Between을 IN-List로 변환할 때는 그래서 데이터 분포나 수직적 탐색 비용을 따져보고 변환하는게 좋다.

3.7 Index Skip Scan 활용

3.6 과 같이 BETWEEN을 IN-List 로 변환하지 않고도 Index Skip Scan을 활용하면 같은 효과를 낼 수 있다.

2018년 1월부터 12월까지 판매데이터가 월별로 10만개(총 120만개)이며 판매구분이 'A'인 데이터가 10만개, 'B'인 데이터가 110만개가 저장되어있는 월별고객별판매집계 테이블이 있다.

select count(*)
from 월별고객별판매집계 t
where 판매구분 = 'A'
AND 판매월 BETWEEN '201801' AND '201812';

이 쿼리를 최적으로 수행하기 위해 '=' 조건인 판매구분이 선두컬럼에 위치하도록 아래와 같이 인덱스를 구성해야 한다.

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

IDX1 인덱스를 사용할 때의 트레이스 결과는 아래와 같으며 인덱스 스캔 시 281개의 블록 I/O가 발생했다. 테이블 액세스는 전혀 발생하지 않는다.

Rows    Row Source Operation
------  ----------------------------------------------------------------------
     1  SORT AGGREGATE (cr=281 pr=0 pw=0 time=47753ms)
100000    INDEX RANGE SCAN 월별고객별판매집계_IDX1 (cr=281 pr=0 pw=0 time=....)

이번엔 BETWEEN 조건의 판매월 컬럼이 선두인 IDX2 인덱스를 사용하는 경우이다.

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

판매구분='A'인 레코드는 2018년 1월부터 12월까지 각 판매월 앞쪽에 위치해 서로 멀리 떨어져있게된다.

IDX2 인덱스를 사용할 때 트레이스의 결과로 인덱스를 스캔하며 3,090개 블록 I/O가 발생했다.(비효율)
이유는 인덱스 선두 컬럼이 BETWEEN 조건이여서 판매구분이 'B'인 레코드까지 모두 스캔하고서 버렸기 때문이다.

select /*+ index(t 월별고객별판매집계_IDX2) */ count(*)
from 월별고객별판매집계 t
where 판매구분 = 'A'
AND 판매월 BETWEEN '201801' AND '201812';

Rows    Row Source Operation
------  ----------------------------------------------------------------------
     1  SORT AGGREGATE (cr=3090 pr=0 pw=0 time=206430 us)
100000    INDEX RANGE SCAN 월별고객별판매집계_IDX2 (cr=3090 pr=0 pw=0 time=....)

이때 IN-List 를 사용해 비효율을 제거할 수도 있지만 IN-List 개수가 많으면 그만큼 인덱스 브랜치 블록을 여러번 반복 탐색하게 되므로 이럴 때 Index Skip Scan을 사용하면 효과적일 수 있다.

select /*+ index_ss(t 월별고객별판매집계_IDX2) */ count(*)
from 월별고객별판매집계 t
where 판매구분 = 'A'
AND 판매월 BETWEEN '201801' AND '201812';

Rows    Row Source Operation
------  ----------------------------------------------------------------------
     1  SORT AGGREGATE (cr=300 pr=0 pw=0 time=94282 us)
100000    INDEX SKIP SCAN 월별고객별판매집계_IDX1 (cr=300 pr=0 pw=0 time=500073 us)

즉, 선두 컬럼이 Between 이어서 나머지 검색 조건을 만족하는 데이터들이 서로 멀리 떨어져 있을 때, Index Skip Scan을 사용해보자

3.8 IN 조건은 '=' 인가

IN 조건은 '=' 조건이 아니다. 인덱스를 [상품ID + 고객번호] 순으로 설계할때와 [고객번호 + 상품ID] 순으로 설계할 때 인덱스를 어떻게 구성하느냐에 따라 성능도 달라질 수 있다.

SELECT *
FROM 고객별가입상품
WHERE 고객번호 = :cust_no
AND 상품ID IN ('NH00037', 'NH00041', 'NH00050')

만약 위의 쿼리에서 인덱스를 [상품ID + 고객번호] 순으로 생성하면 같은 상품은 고객번호순으로 정렬된 상태로 리프 블록에 저장된다. 하지만 고객번호 기준으로는 상품ID에 따라 뿔뿔이 흩어진 상태가 된다.

이런 인덱스 구성에서는 상품 ID 조건절이 IN-List Iterator(IN 조건이 '=' 조건으로 변환되는 것) 방식으로 풀리는 것이 효과적이다.
고객번호 = 1234를 만족하는 레코드가 서로 멀리 떨어져있기 때문이다.

IN-List Iterator 방식으로 풀면 고객번호와 상품 ID 둘 다 인덱스 액세스 조건으로 사용되며, 인덱스를 수직적으로 세 번 탐색하고 그 과정에서 아홉 개 블록을 읽는다.
IN-List Iterator 방식으로 풀지 않으면 상품 ID가 어느 한 지점을 스캔 시작점으로 찾기 어렵고, 인덱스 선두 컬럼인 상황에서 필터 조건으로 사용되므로 테이블 전체 또는 인덱스 전체를 스캔하면서 필터링해야 한다.

인덱스를 만약 [고객번호 + 상품 ID] 순으로 생성하고 위의 쿼리를 실행한다면 고객은 상품ID순으로 정렬된 상태로 같은 리프 블록에 저장된다.

여기서도 상품 ID 조건절을 IN-List Iterator 방식으로 풀면, 인덱스를 수직적으로 세 번 탐색하는 과정에 아홉개 블록을 읽는다.
IN-List Iterator 방식으로 풀지 않으면 상품 ID 조건절은 위와 동일하게 필터 조건으로 처리된다. 하지만 고객번호가 선두컬럼이고, 액세스 조건이며 한블록 또는 연속된 두 블록에 상품ID로 정렬되어 모여있으므로 블록 I/O는 수직적 탐색 과정을 포함해 총 세 개(또는 네 개)만 발생한다.

결론은 IN 조건은 '=' 이 아니며 '=' 이 되기 위해서는 IN-List Iterator 방식으로 풀려야 한다. 그게 아니라면 IN 조건은 필터조건이다.
IN-List Iterator 조건이 항상 효과적인 것만은 아니며 고객별 상품 데이터가 아주 많지 않은 상황에서는 필터 방식이 오히려 나을 수 있다.

😎 NUM_INDEX_KEYS 힌트 활용
인덱스가 [고객번호 + 상품 ID] 순서로 구성된 상황에서 고객번호만 인덱스 액세스 조건으로 사용하려면 NUM_INDEX_KEYS 힌트를 활용 하면 된다.
NUM_INDEX_KEYS힌트의 세번째 인자에 1을 넣으면 인덱스 첫 번째 컬럼까지만 액세스 조건으로 사용하라는 의미이며 2를 넣으면 두번째 컬럼까지 액세스 조건으로 사용된다.

SELECT /*+ num_index_keys(a 고객별가입상품_x1 1) */ *
from 고객별가입상품 a
where 고객번호 = :cust_no
and 상품ID in ('NH00037', 'NH00041', 'NH00050')

또는 힌트를 사용하지 않고 아래와 같이 인덱스 컬럼을 가공하여도 첫번째 컬럼만 액세스 조건으로 사용하게 할 수 있다.

SELECT *
FROM 고객별가입상품
WHERE 고객번호 :CUST_NO
AND RTRIM(상품ID) IN ('NH00037', 'NH00041', 'NH00050')

SELECT *
FROM 고객별가입상품
WHERE 고객번호 = :CUST_NO
AND 상품ID || '' IN ('NH00037', 'NH00041', 'NH00050')

3.9 BETWEEN과 LIKE 스캔 범위

LIKE와 BETWEEN은 둘 다 범위검색 조건으로 사용할 때 비효율 원리가 똑같이 적용된다.
하지만 LIKE 보다는 BETWEEN을 사용하는게 더 낫다.

만약 인덱스가 [판매월 + 판매구분] 순서로 구성되어있고,판매구분 'A', 'B' 두개값이 각 90%, 10% 비중을 차지하는 상황에서 아래 조건절에 대해 인덱스 스캔량을 비교하면 다음과 같다.

<조건절1>
WHERE 판매월 BETWEEN '201901' AND '201912'
AND 판매구분 = 'B'

<조건절2>
WHERE 판매월 LIKE '2019%'
AND 판매구분 = 'B'

조건절 1은 판매월 '201901'이고 판매구분 = 'B'인 첫 번째 레코드에서 스캔을 시작하지만 조건절 2는 판매월 '201901'인 첫 번째 레코드부터 스캔을 시작한다. '201900' 과 같은 값이 있을지도 모르기 때문이다.

이번엔 반대로 판매구분 'A'와 'B'가 각각 10%, 90% 비중을 차지할 때 인덱스 스캔량을 비교해보자

<조건절3>
WHERE 판매월 BETWEEN '201901' AND '201912'
AND 판매구분 = 'A'

<조건절4>
WHERE 판매월 LIKE '2019%'
AND 판매구분 = 'A'

조건절3은 판매월 '201912' 이고 판매구분 'B' 인 첫 번째 레코드를 만나는 순간 스캔을 멈추지만 조건절4는 판매월 = '201912'인 레코드를 모두 스캔하게 된다. 혹시라도 '201913'과 같은 값이 있을 수도 있기 때문이다.

3.10 범위검색 조건을 남용할 때 생기는 비효율

SQL작성 시 사용자 입력과 선택에 따라 조건절을 다양하게 주기위해 옵션(LIKE)으로 처리 할 때 해당 컬럼이 인덱스 구성 컬럼일 때는 주의가 필요하다.

예로 아래는 인덱스가 [회사코드 + 지역코드 + 상품명] 순인 가입상품 테이블을 조회하는 쿼리이다.

-- <쿼리 1> 회사코드, 지역코드, 상품명 모두 입력
SELECT 고객ID, 상품명, 지역코드, ...
FROM 가입상품
WHERE 회사코드 = :com
AND 지역코드 = :reg
AND 상품명 LIKE :prod || '%'

-- <쿼리 2> 회사코드, 상품명만 입력
SELECT 고객 ID, 상품명, 지역코드, ...
FROM 가입상품
WHERE 회사코드 = :com
AND 상품명 LIKE :prod || '%'

위 쿼리 1에서 사용자가 회사코드, 지역코드, 상품명을 각각 'C70', '02', '보급'으로 입력하고 조회했을 때, 쿼리 2에서 지역코드를 제외하고 입력했을 때 스캔 범위는 다음과 같다.

<쿼리1>은 세 컬럼이 모두 액세스 조건으로 아주 적은 범위를 스캔하여 빠르며, <쿼리 2>는 중간 컬럼 조건이 없어 어쩔 수 없이 넓은 범위를 스캔한다.

만약 지역코드까지 옵션으로 처리를 한다면 어떻게될까?

SELECT 고객ID, 상품명, 지역코드, ...
FROM 가입상품
WHERE 회사코드 = :com
AND 지역코드 LIKE :reg || '%'
AND 상품명 LIKE :prod || '%'

위 쿼리에서 세 컬럼에 값을 모두 입력 했을 때와, 지역코드를 입력하지 않고 조회했을 때의 스캔범위는 다음과 같다.

지역코드를 입력 안 한 경우(우측)는 위와 동일하지만 지역코드를 입력한 경우 전에 비해 인덱스 스캔 범위가 늘어났다.
지역코드를 옵션 처리하게 되면서 액세스 조건이던 상품명이 필터 조건으로 변경되었기 때문이다.

또다른 예시로 옵션 처리를 위해 모든 조건절을 BETWEEN으로 처리하는 경우도 존재하는데 코딩을 쉽게 하기 위해 이처럼 인덱스 컬럼에 범위 검색 조건을 남용하면 인덱스 스캔 비효율이 생기며, 대량 테이블을 넓은 범위로 검색할 땐 그 영향이 매우 클 수 있으므로 인덱스 컬럼에 대한 비교 연산자를 신중하게 선택해야 한다.

SELECT 거래일자, 종목코드, 투자자유형코드, 주문매체코드, 체결건수, 체결수량, 거래대금
FROM 일별종목거래
WHERE 거래일자 BETWEEN :시작일자 AND :종료일자 -- 필수 조건
AND 종목코드 BETWEEN :종목1 AND :종목2 -- 옵션 조건
AND 투자자유형코드 BETWEEN :투자자유형1 AND :투자자유형2 -- 옵션 조건
AND 주문매체구분코드 BETWEEN :주문매체구분1 AND :주문매체구분2 -- 옵션 조건

3.11 다양한 옵션 조건 처리 방식의 장단점 비교

위 두가지 옵션 조건 외에도 다양한 방식의 옵션 처리가 존재한다.

😎 OR 조건 활용

SELECT * 
FROM 거래
WHERE (:cust_id is null or 고객ID = :cust_id)
and 거래일자 between :dt1 and :dt2

옵티마이저의 OR Expansion 쿼리 변환이 기본적으로 작동하지 않으므로 인덱스가 [고객ID + 거래일자] 와 같은 구성해도 인덱스를 사용할 수 없다.
그러므로 인덱스 선두 컬럼에 대한 옵션 조건에 OR 조건을 사용해선 안된다.

[거래일자 + 고객ID] 로 인덱스를 구성한다고 하면 인덱스는 사용할 수 있지만 고객 ID가 인덱스 필터 조건이 아닌 테이블 액세스 필터 조건으로 사용되어 엄청난 비효율을 초래할 수 있다.

➡️ OR 조건을 활용한 옵션 주의사항 정리
- 인덱스 액세스 조건으로 사용 불가
- 인덱스 필터 조건으로도 사용 불가
- 테이블 필터 조건으로만 사용 가능
- 단 인덱스 구성 컬럼 중 하나 이상이 Not Null 컬럼이면, 18c부터 인덱스 필터 조건으로 사용 가능

결론은 OR 조건을 이용한 옵션 조건 처리는 가급적 사용하지 않는 것이 좋다.

참고로 아래와 같은 형태의 OR 조건절은 OR-Expansion을 통해 인덱스 사용이 가능하다.

SELECT * 
FROM 거래
WHERE 고객 ID = :cust_id
AND ((:dt_type = 'A' AND 거래일자 BETWEEN :dt1 AND :dt2)
	or
    (:dt_type = 'B' AND 결제일자 BETWEEN :dt1 AND :dt2))

😎 LIKE/BETWEEN 조건 활용
변별력이 좋은(소수) 필수 조건 컬럼이 인덱스 선두에 있고 LIKE/BETWEEN을 인덱스 필터 조건으로 사용한다면 충분히 좋은 성능을 낼 수 있다.

-- [등록일시 + 상품분류코드] 순의 인덱스
SELECT *
FROM 상품
WHERE 등록일시 >= TRUNC(SYSDATE) -- 필수 조건(당일 등록)
AND 상품분류코드 LIKE :prd_cls_cd || '%' -- 옵션 조건

더구나 필수조건이 '=' 이면 옵션 조건도 인덱스 액세스 조건이 되므로 최적의 성능을 낼 수 있다.

-- [상품명 + 상품분류코드] 순의 인덱스
SELECT *
FROM 상품
WHERE 상품명 = :prd_nm -- 필수조건
AND 상품분류코드 LIKE :prd_cls_cd || '%' -- 옵션 조건

만약 필수 조건이 변별력이 좋지 않을 때(다수) 아래 SQL 실행 시 상품대분류코드만으로 조회하면 Table Full Scan이 유리하지만 옵티마이저는 상품코드까지 입력했을 때 기준으로 Index Range Scan을 선택한다.
사용자가 상품 코드까지 입력한다면 최적의 성능을 내지만 입력하지 않으면 성능에 문제가 생길 수 있다.

-- 인덱스 : 상품대분류코드 + 상품코드
SELECT *
FROM 상품
WHERE 상품대분류코드 = :prd_lcls_cd -- 필수조건
AND 상품코드 LIKE :prd_cd || '%' -- 옵션 조건

➡️ LIKE/BETWEEN 조건을 활용한 옵션 주의사항
1. 인덱스 선두 컬럼에 대한 옵션으로 LIKE/BETWEEN 처리는 금물
: 선두 컬럼에 입력값이 없으면 비효율 발생
2. NULL 허용 컬럼에 대한 옵션으로 LIKE/BETWEEN 처리 금물
: 성능뿐 아니라 결과집합에도 오류 생김 (NULL값 누락)
3. 숫자형 컬럼에 대한 옵션으로 LIKE 처리 금물
: LIKE 로 인해 조건 컬럼이 문자형으로 자동 형변환되어 해당 컬럼이 선두 컬럼이면 인덱스 사용 불가
4. 가변 길이 컬럼에 대한 옵션으로 LIKE 처리 금물
: 예를들어 '김훈' 을 찾기위해 LIKE '%' 조건 사용 시 '김훈남' 고객도 같이 조회된다.
따라서 컬럼 값 길이가 가변적일 때는 변수 값 길이가 같은 레코드만 조회되도록 아래와 같은 조건절을 추가한다.

WHERE 고객명 LIKE :cust_nm || '%' 
AND LENGTH(고객명) = LENGTH(NVL(:cust_nm, 고객명))

또는 아래와 같이 사용하여 고객명을 입력하지 않을 때 :cust_nm에 '%'을 입력해서 모든 고객명을 출력하도록 한다.

WHERE 고객명 LIKE :cust_nm

😎UNION ALL 활용
UNION ALL을 이용해 :cust_id 값 입력 여부에 따라 위아래 SQL 중 어느 하나만 실행되게도 할 수 있다.

SELECT *
FROM 거래
WHERE :cust_id IS NULL
UNION ALL
SELECT *
FROM 거래
WHERE :cust_id IS NOT NULL
AND 고객ID = :cust_id
AND 거래일자 BETWEEN :dt1 AND :dt2

위 방식으로 사용하면 코딩량이 증가한다는 단점이 있지만 변수에 값을 입력하든 안하든 인덱스를 가장 최적으로 사용하게 된다.

😎 NVL/DECODE 함수 활용
NVL, DECODE 함수도 옵션 조건에 많이 사용하는 패턴 중 하나이다.

-- 1. NVL 방식
SELECT *
FROM 거래
WHERE 고객ID = NVL(:cust_id, 고객ID)
AND 거래일자 BETWEEN :dt1 AND :dt2

-- 2. DECODE 방식
SELECT *
FROM 거래
WHERE 고객ID = decode(:cust_id, null, 고객ID, :cust_id)
AND 거래일자 BETWEEN :dt1 AND :dt2

위 SQL 실행계획을 살펴보면 옵티마이저가 OR Expansion인 UNION ALL 방식으로 쿼리가 변환된 것을 확인할 수 있다.

Execution Plan
-----------------------------------------------------------------------------------------
0		SELECT STATEMENT Optimizer=ALL_ROWS
1	0	  CONCATENATION
2	1	    FILTER	-- :cust_id is null
3	2	      TABLE ACCESS (BY LOCAL INDEX ROWID) OF '거래' (TABLE)
4	3	        INDEX (RANGE SCAN) OF '거래_IDX1' (INDEX) -- 거래일자
5 	1		FILTER	-- :cust_id is not null
6	5		  TABLE ACCESS (BY LOCAL INDEX ROWID) OF '거래' (TABLE)
7	6			INDEX (RANGE SCAN) OF '거래_IDX2' (INDEX) -- 고객ID + 거래일자

NVL, DECODE 함수에 대한 OR Expansion을 가능하게 하려면 히든 파라미터 'or_expand_nvl_predicate'가 활성화 되어야한다. 이 기능이 작동하지 않으면 해당 패턴에서 인덱스 액세스 조건으로 사용할 수 없다.

이 방식의 장점은 옵션 조건 컬럼을 인덱스 액세스 조건(UNION ALL로 작용)으로 사용할 수 있지만 단점은 NULL 허용 컬럼을 사용할 수 없다.(결과값 누락)
또한 옵션 조건 처리용 NVL/DECODE 함수를 여러 개 사용하면 그 중 변별력이 가장 좋은 컬럼 기준으로 한번만 OR Expansion이 일어나며 나머지는 인덱스 컬럼이어도 모두 필터 조건 처리되므로 주의해야한다.

⭐Dynamic SQL

Dynamic SQL을 이용하면 옵션 조건에 '=' 연산자를 사용할 수 있으며, 변별력 있는 컬럼을 액세스 조건으로 사용할 수 있게 인덱스만 잘 구성해주면 된다.

select *

from 거래

where 거래일자 between #dt1# and #dt2#

고객ID = #cust_id#

</isNotEmpty>

</select>

- 이때 Dynamic SQL 에 옵티마이저 힌트를 명시하면 동적으로 구성된 조건절과 서로 상충해 오히려 성능 문제가 생길 수 있으니 주의해서 사용해야한다.

- 또한 하드파싱에 의한 성능 문제가 발생하지 않도록 바인드 변수를 잘 사용해야 한다.

3.12 함수호출부하 해소를 위한 인덱스 구성

😎 PL/SQL 함수의 성능적 특성
PL/SQL 사용자 정의 함수는 개발자들이 일반적으로 생각하는 것보다 매우 느리다.

-- 1. 한건 조회
SELECT 회원번호, 회원명, 생년, 생월일, encryption(전화번호)
from 회원
where 회원번호 = :member_no

-- 2. 수십 ~ 수백 만 건 조회
select 회원번호, 회원명, 생년, 생월일, encryption(전화번호)
from 회원
where 생월일 like '01%'

1번 쿼리처럼 한 건 조회 시 함수를 사용하지 않았을 때와 비교해 성능 차이를 크게 느끼지 못하지만 2번 쿼리처럼 대량 데이터 조회 시 성능 차이를 확연하게 느낄 수 있다.

➡️ PL/SQL 사용자 정의 함수가 느린 이유
1. 가상머신(VM) 상에서 실행되는 인터프리터 언어
: Java 언어처럼 프로시저 컴파일 시 바이트코드를 생성해 데이터 딕셔너리에 저장하며, 런타임 시 PL/SQL 엔진이 바이트코드를 해석하면서 실행함
2. 호출 시마다 컨텍스트 스위칭 발생
: 매번 SQL 실행엔진과 PL/SQL 가상머신 사이에 컨텍스트 스위칭이 일어남
3. 내장 SQL에 대한 Recursive Call 발생(세 원인 중 부하가 가장 큼)
: 100만건의 데이터를 조회하는 쿼리에 사용자 정의 함수 실행 시 그 함수에 SQL이 내장되어 있다면 그 내장 SQL도 100만번 실행된다.

PL/SQL 함수를 쓰지 않고 조인문으로 처리하면 성능 차이가 매우 클 수 있다.

SELECT 회원번호, 회원명, 생년, 생월일, GET_ADDR(우편번호) AS 기본주소
FROM 회원
WHERE 생월일 LIKE '01%'
-------------------------------------------------------------------------

SELECT a.회원번호, a.회원명, a.생년, a.생월일
	, (SELECT b.시도 || ' ' || b.구군 || ' ' || b.읍면동
    	FROM 기본주소 b
        WHERE b.우편번호 = a.우편번호
        AND b.순번 = 1) 기본주소
FROM 회원 a
WHERE a.생월일 LIKE '01%'

-- 또는 

SELECT a.회원번호, a.회원명, a.생년, a.생월일
	,  b.시도 || ' ' || b.구군 || ' ' || b.읍면동
FROM 회원 a, 기본주소 b
WHERE a.생월일 like '01%'
AND b.우편번호(+) = a.우편번호
AND b.순번(+) = 1

만약 내부 로직이 너무 복잡해 PL/SQL 함수를 그대로 쓸 수 밖에 없을 때 액세스 조건을 고려한 인덱스 구성으로 함수 호출 횟수를 줄일 수 있다.

😎 효과적인 인덱스 구성을 통한 함수 호출 최소화

조건절에 아래와 같이 PL/SQL 함수를 사용했을 때 Full Scan 방식으로 읽으면 해당 함수는 테이블 건수만큼 수행된다.

SELECT /*+ full(a) */ 회원번호, 회원명, 생년, 생월일, 등록일자
FROM 회원
WHERE 암호화된_전화번호 = encryption(:phone_no)

아래와 같이 다른 조건절이 있다면 PL/SQL 함수는 그 조건절을 만족하는 건수만큼 수행된다.

SELECT /*+ full(a) */ 회원번호, 회원명, 생년, 생월일, 등록일자
FROM 회원
WHERE 생년 = '1987'
AND 암호화된_전화번호 = encryption(:phone_no)

<함수 호출 최소화를 위한 인덱스 구성예시>

예시1. 아래와 같이 생년 단일 컬럼으로 인덱스를 구성하여 실행 시 encryption 함수는 테이블 액세스 횟수 즉, 생년= '1987' 조건을 만족하는 건수만큼 수행된다.

CREATE INDEX 회원_X01 ON 회원(생년);

SELECT /*+ INDEX(a 회원_X01) */ 회원번호, 회원명, 생년, 생월일, 등록일자
FROM 회원
WHERE 생년 = '1987'
AND 암호화된_전화번호 = encryption(:phone_no)

예시2. 아래와 같이 [생년+생월일+암호화된 전화번호] 순의 인덱스를 구성하여 실행 시 암호화된 전화번호는 선행 컬럼인 생월일에 대한 '=' 조건이 없으므로 인덱스 필터 조건이다.
따라서 encryption 함수는 인덱스 스캔 횟수 즉, 생년 = '1987' 조건을 만족하는 건수만큼 수행된다.

CREATE INDEX 회원_X02 ON 회원(생년, 생월일, 암호화된_전화번호);

SELECT /*+ INDEX(a 회원_X02) */ 회원번호, 회원명, 생년, 생월일, 등록일자
FROM 회원
WHERE 생년 = '1987'
AND 암호화된_전화번호 = encryption(:phone_no)

예시3. 아래와 같이 [생년 + 암호화된 전화번호] 순의 인덱스를 구성하여 실행 시 암호화된 전화번호도 생년과 함께 인덱스 액세스 조건으로 사용되어 encryption 함수는 한번만 수행된다.

CREATE INDEX 회원_X03 ON 회원(생년, 암호화된_전화번호);

SELECT /*+ INDEX(a 회원_X03) */ 회원번호, 회원명, 생년, 생월일, 등록일자
FROM 회원
WHERE 생년 = '1987'
AND 암호화된_전화번호 = encryption(:phone_no)