3.3 인덱스 스캔 효율화

3.3.1 인덱스 탐색

2.1(.2)(https://gyujingyujin.tistory.com/14?category=1262761) 참고
(page173 예시 참고)

---

3.3.2 인덱스 스캔 효율성

(page180 예시 참고)

---

3.3.3 액세스 조건과 필터 조건

• 인덱스 액세스 조건
  1. 인덱스 스캔 범위를 결정하는 조건절.
  2. 인덱스 수직적 탐색을 통해 스캔 시작점을 결정하는데 영향을 미치고, 인덱스 리프 블록을 스캔하다가 어디서 멈출지 결정한다.
• 인덱스 필터 조건
  테이블로 액세스할지를 결정하는 조건절.
• 테이블 필터 조건
  쿼리 수행 다음 단계로 전달하거나 최종 결과집합에 포할할지를 결정한다.
• 인덱스를 이용한 테이블 액세스 비용
= 인덱스 수직적 탐색 비용 + 인덱스 수평적 탐색 비용 + 테이블 랜덤 액세스 비용
= 인덱스 루트와 브래치 레벨에서 읽는 블록 수 + 인덱스 리프 블록을 스캔하는 과정에서 읽는 블록 수 + 테이블 액세스 과정에 읽는 블록 수.

---

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

테이블과 달리 인덱스에는 '같은 값'을 갖는 레코드들이 서로 군집해 있다. 인덱스 컬럼을 앞쪽부터 누락없이 '=' 연산자로 조회하면 조건절을 만족하는 레코드는 모두 모여 있다. 어느 하나를 누락하거나 '=' 조건이 아닌 연산자로 조회하면 조건절을 만족하는 레코드가 서로 흩어진 상태가 된다.
선행 컬럼이 모두 '=' 조건인 상태에서 첫 번째 나타나는 범위검색 조건까지만 만족하는 인덱스 레코드는 모두 연속해서 모여 있지만, 그 이하 조건까지 만족하는 레코드는 비교 연산자 종류에 상관없이 흩어진다. (page187 예시 참고)
선행 컬럼이 모두 '=' 조건인 상태에서 첫 번째 나타나는 범위검색 조건이 인덱스 스캔 범위를 결정한다. 따라서 이들이 인덱스 액세스 조건이다. 나머지 인덱스 컬럼 조건은 모두 인덱스 필터 조건이다.(실행계획에는 인덱스 컬럼에 대한 조건절은 모두 액세스 조건에 표시된다.)

---

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

인덱스 선행 컬럼이 조건절에 없거나 부등호, BETWEEN, LIKE 같은 범위 검색 조건이면, 인덱스를 스캔하는 단계에서 비효율이 생긴다.
예를들어 선두컬럼에 BETWEEN 연산자를 사용할 경우 나머지 조건을 만족하는 레코드들이 뿔뿔히 흩어져 있게 된다. 따라서 조건을 만족하지 않는 레코드까지 스캔하고서 버리는 비효율이 생긴다.
다행스러운 것은 BETWEEN 조건절의 시작과 끝 값에는 전체를 다 읽지 않고 조건을 만족하는 첫 번째 레코드부터 읽기 시작한다.(BETWEEN 조건절 마지막 값 구간에서도 전체를 다 읽지 않고 조건절보다 큰 값을 만나는 순간 스캔을 멈춘다.)
(page191 예시 참고)

---

3.3.6 BETWEEN을 IN-List로 전환

인덱스의 선행 컬럼이 범위 검색 컬럼일 경우 IN_List로 바꿔주면 큰 효과를 얻는 경우가 있다.

• BETWEEN 사용 시
  아래 그림처럼 조건을 만족하지 않는 레코드까지 스캔하게 된다.

  

• IN-List 사용 시
  아래 그림처럼 인덱스 수직적 탐색의 2번 발생하여 스캔하게 된다.

  

이는 곧 IN-List 개수 만큼 UNION ALL 브랜치가 생성되고 각 브랜치마다 모든 컬럼을 '=' 조건으로 검색하므로 앞서 선두 컬럼에 BETWEEN을 사용할 때와 같은 비효율이 사라진다.

BETWEEN 조건을 IN-List로 전환할 때 주의 사항

• IN-List 개수가 많지 않아야 한다.
• 많은 IN-List 개수로 인해서 수직적 탐색이 많이 발생하게 되면 BETWEEN 조건 때문에 리프 블록을 스캔하는 비효율보다 In-List 개수만큼 브랜치 블록을 반복 탐색하는 비효율이 더 클 수 있다. 루트에서 브랜치 블록까지 Depth가 깊을 때도 그렇다.
• 인덱스 스캔 과정에 선택되는 레코드들이 서로 멀리 떨어져 있을 때만 유용하다.

인덱스 리프 블록에는 테이블 블록과 달리 매우 많은 레코드가 담기기 때문에 범위 검색 조건으로 인해 비효율적으로 스캔하더라도 블록 I/O 측면에서는 대개 소량에 그치는 경우가 많다.

---

3.3.7 Index Skip Scan 활용

BETWEEN을 IN-List 조건으로 변환하지 않고 2.3.4(https://gyujingyujin.tistory.com/16?category=1262761) 에서 배운 Index Skip Scan을 활용하여 같은 효과를 낼 수 있다.(page 199 참고)
선두컬럼이 BETWEEN이어서 나머지 검색 조건을 만족하는 데이터들이 서로 멀리 떨어져 있을 때 유용하다.

---

3.3.8 IN조건은 '='인가

IN 조건이 '='이 되려면 IN-List Iterator 방식으로 풀려야만 한다. 그렇지 않으면, IN 조건은 필터 조건이다. 하지만 액세스 조건으로 만들기 위해 IN-List Iterator 방식으로 푸는 것이 항상 효과적인건 아니다. 액세스 방식이 아닌 필터 방식으로 처리되는 게 더 나을 때도 있다.(page206 참고)

NUM_INDEX_KEYS 힌트 활용

select    /*+ num_index_keys(a 고객별가입상품_X1 1) */

힌트의 세 번째 인자 '1'은 인덱스 첫 번째 컬럼까지만 액세스 조건으로 사용하라는 의미다.(page 207 참고)

---

3.3.9 BETWEEN과 LIKE 스캔 범위 비교

결론부터말하자면 LIKE보다 BETWEEN을 사용하는게 낫다.

/* <조건절 1> */
where    판매월 BETWEEN '202201' and '202212'
and        판매구분 = 'B';

/* <조건절 2> */
where    판매월 LIKE '2022%'
and        판매구분 = 'B';

조건절 1은 판매월 202201 이고 판매구분 = 'B'인 첫 번째 레코드에서 스캔을 시작하는 반면, 조건절 2는 판매월 = '202201'인 첫 번째 레코드에서 스캔을 시작한다. '202200'이 저장될 경우도 있기 때문에 판매구분 = 'B'인 지점으로 바로 갈 수 없다.

---

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

범위검색 조건을 사용할 경우 더 이상 액세스 조건으로 사용할 수 없고 필터 조건으로 상용되기 때문에 스캔 범위가 늘어난다.(page 212 '꼭' 참고)

---

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

OR, LIKE/BETWEEN, UNION ALL, NVL/DECODE 방식의 장단점을 비교해 보자.

OR 조건 활용

인덱스에 포함되지 않은 컬럼에 대한 옵션 조건은 어차피 테이블에서 필터링할 수 밖에 없으므로 그럴 때는 OR 조건을 사용해도 무방하다.옵티마이저에 의한 OR Expansion 쿼리 변환이 기본적으로 작동하지 않는다. OR 조건을 활용한 옵션 조건 처리는 다음과 같다.

• 인덱스 액세스 조건으로 사용 불가
• 인덱스 필터 조건으로도 사용 불가
• 테이블 필터 조건으로만 사용 가능
• 단, 인덱스 구성 컬럼 중 하나 이상이 Not Null 컬럼이면, 18c부터 인덱스 필터 조건으로 사용 가능

이러한 이유로 OR 조건을 이용한 옵션 조건 처리는 가급적 자제해야 한다.

LIKE/BETWEEN 조건 활용

변별력 좋은 필수 조건 컬럼을 인덱스 선두에 두고 액세스 조건으로 사용하면, LIKE/BETWEEN이 인덱스 필터 조건으로 충분히 좋은 성능을 낼 수 있다. 하지만 필수 조건의 변별력이 좋지 않을 경우 Table Full Scan이 유리하다.(필수 조건의 변별력이 좋지 않아도 옵티마이저는 Index Range Scan 선택) 아래 1,2는 LIKE/BETWEEN, 나머지는 LIKE 조건 사용 시 고려사항이다.)

1. 인덱스 선두 컬럼 (LIKE/BETWEEN 조건 사용 시 고려)
   인덱스 선두 컬럼에 대한 옵션 조건을 LIKE/BETWEEN 연산자로 처리하는 것은 금물이다.

   select *
   from dual
   where LIKE :var || '%';

2. NULL 허용 컬럼 (LIKE/BETWEEN 조건 사용 시 고려)
   NULL 허용 컬럼에 대한 옵션 조건을 LIKE/BETWEEN 연산자로 처리하는 것은 금물이다. 아래 코드처럼 조회할 경우 컬럼 값이 NULL 인 데이터는 결과집합에서 누락된다.

   select *
   from 거래
   where 고객ID LIKE '%'
   and 거래일자 between :dt1 and :dt2

3. 숫자형 컬럼 (LIKE 조건 사용 시 고려)
   숫자형이면서 인덱스 액세스 조건으로도 사용 가능한 컬럼에 대한 옵션 조건 처리는 LIKE 방식을 사용해선 안된다.
   예를 들어, 인덱스가 [거래일자 + 고객ID] 일 때, 고객ID가 문자형이면 :cust_id에 값을 입력할 경우 두 컬럼 모두 인덱스 액세스 조건으로 사용된다.그런데 만약 거래ID가 숫자형 컬럼이면 자동 형변환이 일어나므로 고객ID가 필터 조건으로 사용된다. [고객ID + 거래일자]순으로 구성한 인덱스는 아예 사용할 수 없다. 자동 형변환은 2.2.7(https://gyujingyujin.tistory.com/15?category=1262761)) 참고.

   select *
   from 거래
   where 거래일자 = :trd_dt
   and 거래ID like :cust_dt==id || '%' -- to_char(거래ID) like :cust_id || '%' 와 같다

4. 가변 길이 컬럼 (LIKE)
   LIKE를 옵션 조건에 사용할 때는 컬럼 값 길이가 고정적이어야 한다.
   길이가 일정하지 않을 경우 예를 들어 '김훈' 검색 시 '김훈남'도 같이 조회된다.

   where 고객명 like :cust_nm || '%' -- :cust_nm = '김훈'

   컬럼 값 길이가 가변적일 경우는 아래와 같은 조건절을 추가해야 한다. 또는'%'가 없는 LIKE 조건은 '=' 조건처럼 출력한다. 단 사용자가 고객명을 입력하지 않으면 '%'가 대신 입력 돼야 모든 고객명을 출력한다.

   and length(고객명) = length(nvl(:cust_nm, 고객명))

UNION ALL 활용

NULL 허용 컬럼도 사용하는 데 전혀 문제 없다.(page 220 예시 꼭 참고)
유일한 단점은 코드길이가 길어지는 것 뿐이다.

NVL/DECODE 함수 활용

컬럼을 함수 인자로 사용(인덱스 컬럼 가공)했어도 NVL, DECODE 함수에 대한 OR Expansion을 가능케하는 히든 파라미터가 존재하기 때문에 OR Expansion 이 발생한다.(Coalesce 함수, CASE문 x) -> NVL/DECODE 함수를 여러 개 사용하면 그중 변별력이 가장 좋은 컬럼 기준으로 한 번만 OR Expansion이 일어난다.
OR Expansion 기준으로 선택되지 않으면 인덱스 구성 컬럼이어도 모두 필터 조건으로 처리된다. 때문에 NVL/DECODE 함수의 장점에도 불구하고 모든 옵션 조건을 이 방식으로 처리할 수 없다.
단점은, LIKE 패턴처럼 NULL 허용 컬럼에 사용할 수 없다. 조건절 변수에 NULL을 입력하면 값이 NULL인 레코드는 결과집합에서 누락되기 때문이다.

select * from dual where NULL = NULL; /* 선택된 레코드가 없습니다. */

select * from dual where NULL IS NULL /* 정상 출력 */

---

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

(page 225 참고)