인덱스 스캔 효율

인덱스 탐색

• 수직적 탐색, 수평적 탐색

인덱스 스캔 효율성

• 인덱스 선행 컬럼이 조건절에 없거나 '=' 조건이 아니면 인덱스 스캔 과정에서 비효율 발생

액세스 조건과 필터 조건

1. 액세스 조건
   • 인덱스 스캔 범위를 결정하는 조건절
   • 인덱스 수직적 탐색을 통해 시작점을 결정하는데 영향을 미침
   • 인덱스 리프 블록을 스캔하다가 어디서 멈출지를 결정하는데 영향을 미치는 조건절
2. 필터 조건
   - 테이블로 액세스할지를 결정하는 조건절

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

   • 비용 = 인덱스 수직적 탐색 비용 + 인덱스 수평적 탐색 비용 + 테이블 랜덤 액세스 비용
             = 인덱스 루트와 브랜치 레벨에서 읽는 블록 수 +
                인덱스 리프 블록을 스캔하는 과정에 읽는 블록 수 +
                테이블 액세스 과정에서 읽는 블록 수

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

• 인덱스에는 같은 값을 갖는 레코드들이 서로 군집해 있음
• 범위검색 조건까지만 만족하는 인덱스 레코드는 모두 연속해서 모여 있지만, 그 이하 조건까지 만족하는 레코드는 비교 연산자 종류에 상관없이 흩어짐
• 필터 조건도 범위검색 조건 맨 처음과 마지막 구간에서는 스캔량 줄이는 데 역할을 함.(대개 무시할만한 수준)

  인덱스 컬럼에 대한 조건절은 모두 액세스 조건에 표시

  1. 좌변 컬럼을 가공한 조건절
  2. 왼쪽 '%' 또는 양쪽 '%' 기호를 사용한 like조건절
  3. 같은 컬럼에 대한 조건절이 두개 이상일 때, 인덱스 액세스 조건으로 선택되지 못한 조건절
  4. OR Expansion 또는 INLIST ITERATOR로 선택되지 못한 OR 또는 IN 조건절

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

• 인덱스 컬럼을 조건절에 모두 등치(=) 조건으로 사용할 때 효율이 가장 좋다.
• 반면, 인덱스 선행 컬럼이 조건절에 없거나 부등호, BETWEEN, LIKE 같은 범위검색 조건이면, 인덱스를 스캔하는 단계에서 비효율이 발생
• 인덱스 선행 컬럼이 모두 '='조건일 때 필요한 범위만 스캔하고 멈출수 있는 것은 조건을 만족하는 레코드가 모두 한데 모여 있기 때문

BETWEEN을 IN-LIST로 전환

• 범위검색 컬럼이 맨 뒤로 가는 인덱스(BETWEEN 조건을 IN-List로 바꾸면 큰 효과를 얻음)
• In-List 개수 만큼 UNION ALL 브랜치가 생성되고 각 브랜치마다 모든 컬럼을 '='조건으로 검색
• In-List 개수가 늘어날 수 있다면 BETWEEN 조건을 IN-List로 전환하는 방식은 사용하기 곤란

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

  • IN-List 개수가 많지 않아야 한다.
  • 인덱스 스캔과정에 선택되는 레코드들이 서로 멀리 떨어져 있을 때만 유용
  • BETWEEN 조건 때문에 인덱스를 비효율적으로 스캔하더라도 블록 I/O 측면에서는 대개 소량에 그치는 경우가 있음

Index Skip Scan 활용

• 선두 컬럼이 BETWEEN이어서 나머지 검색 조건을 만족하는 데이터들이 서로 멀리 떨어져있을 때 Index Skip Scan의 위력이 나타남.

IN 조건은 '='인가

• IN 조건은 '='이 아님
• 인덱스를 어떻게 구성하느냐에 따라 성능이 달라짐
• IN 조건은 필터 조건이다.
• NUM_INDEX_KEY 힌트 : 인덱스 액세스 조건으로 사용
• NUM_INDEX_KEY 힌트의 세번째 인자는 인덱스 n번째 컬럼까지만 액세스 조건으로 사용하라는 의미

/*+ num_index_keys(테이블 컬럼 명 n)

BETWEEN과 LIKE 스캔 범위 비교

• LIKE와 BETWEEN은 둘다 범위검색 조건
• 범위검색 조건을 사용할 때의 비효율이 똑같이 적용됨.
• 데이터 분포와 조건절 값에 따라 인덱스 스캔량이 서로 다를 수 있음
• LIKE보다 BETWEEN을 사용하는게 낫다.

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

• 인덱스 스캔 비효율이 성능에 미치는 영향이 적을 수도 있지만, 대량의 테이블을 넓은 범위로 검색할 때는 그 영향이 매우 클 수도 있다.
• 데이터 분포에 따라 인덱스 컬럼에 대한 비교 연산자를 신중하게 선택해야 함.

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

1. OR 조건 활용
   • 인덱스 액세스 조건으로 사용 불가
   • 인덱스 필터 조건으로도 사용 불가
   • 테이블 필터 조건으로만 사용 가능
   • 단, 인덱스 구성 컬럼 중 하나 이상이 Not Null 컬럼이면, 18c부터 인덱스 필터 조건으로 사용 가능
2. LIKE/BETWEEN 조건 활용
   • 인덱스 선두 컬럼 사용 금지
   • NULL 허용 컬럼 사용 금지
   • 숫자형 컬럼 사용 금지
   • 가변 길이 컬럼 사용 금지
3. UNIONALL 활용
   • 옵션 조건 컬럼도 인덱스 액세스 조건으로 사용
   • NULL 허용 컬럼이더라도 사용하는데 문제 없음
   • 유일한 단점은 코딩량이 길어짐
4. NVL/DECODE 함수 활용
   • 옵션 조건 컬럼을 익데스 액세스 조건으로 사용할 수 있다.
   • NVL/DECODE 함수를 여러개 사용하면 그중 변별력이 가장 좋은 컬럼 기준으로 한 번만 OR Expansion이 일어난다.
   • 따라서 OR Expansion 기준으로 선택되지 않으면 인덱스 구성 컬럼이어도 모두 필터 조건으로 처리된다.

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

1. PL/SQL 함수의 성능적 특성

• 개발자들이 일반적으로 생각하는 것보다 매우 느리다.

  PL/SQL 사용자 정의 함수가 느린 이유

  1. 가상머신상에서 실행되는 인터프리터 언어
  2. 호출 시마다 컨텍스트 스위칭 발생
  3. 내장 SQL에 대한 Recursive Call 발생(성능을 떨어뜨리는 가장 결정적이 요소)