3.3 인덱스 스캔 효율화

3.3.1 인덱스 탐색

• 책 참고(p173 ~ p183)
  

3.3.2 인덱스 스캔 효율성

• 인덱스 스캔 효율이 좋은지 않은지는 SQL 트레이스로 확인가능하다

3.3.3 액세스 조건과 필터 조건

1.인덱스 액세스 조건

• 인덱스 스캔 범위를 결정하는 조건절이다.
• 인덱스 수직적 탐색을 통해 스캔 시작점을 결정하는데 영향을 미치고,
• 인덱스 리프 블록을 스캔하다가 어디서 멈출지를 결정하는데 영향을 미치는 조건절이다.

2.인덱스 필터 조건

• 테이블로 액세스 할지를 결정하는 조건절이다.

3.테이블 필터 조건

• 쿼리 수행 다음 단계로 전달하거나 최종 결과집합에 포함할지를 결정한다.

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

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

• 선행 컬럼인 = 일때 데이터는 군집되어 있다.
• 중간 컬럼이 범위 검색인 경우 그 뒤 레코드들은 흩어지게 된다(해당 범위 내에서 - 결국 범위 증가)
• 선행 컬럼이 = 조건인 상태에서 첫 번째 나타나는 범위검색 조건이 인덱스 스캔 범위를 결정한다.

범위 검색 이후 = 조건은 맨 처음과 마지막 구간에서의 스캔량을 줄여주므로 인덱스 액세스 조건에 전부 적용되기는 한다.

쉽게 이해하기 위해 첫번째 나타나는 범위 검색 조건까지가 인덱스 액세스 조건이고, 나머지는 필터 조건으로 이해하자

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

• 인덱스 선행 컬럼이 조건절에 없거나 부등호, BETWEEN, LIKE 같은 범위 검색 조건이면, 인덱스를 스캔하는 단계에서 비효율이 생긴다.(책 p192 ~ p193 참조)

3.3.6 BETWEEN 을 IN-List 로 전환

• BETWEEN 을 IN-List 로 전환 시 각각의 수직적 탐색을 통해 효율을 증가시킬 수 있다.
• BETWEEN 을 = 로 바꾸고 union all 로 합친다.
• Index Skip Scan 방식과 비슷하다
• IN-List 항목 개수가 늘어난다면 NL 방식의 조인문이나 서브쿼리로 구현하면 된다.

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

• IN-List 개수가 많지 않아야 한다
• 루트에서 브랜치 블록까지 Depth 가 깊을 수록 비효율적이다.
• 레코드들이 서로 멀리 떨어져 있을때만 유용하다
• 인덱스 리프 블록에는 매우 많은 레코드가 담기기 때문에 데이터 군집성을 파악하고 적용시키자

3.3.7 Index Skip Scan 활용

• 선두 컬림이 BETWEEN 이어서 나머지 검색 조건을 만족하는 데이터들이 서로 멀리 떨어져 있을때, Index Skip Scan 이 유용할 수 있다.
• In List 방식을 안쓰고 BETWEEN 을 선두 컬럼으로 사용하면 스캔량이 많아진다

3.3.8 IN 조건은 '='인가

• IN 조건은 '=' 이 아니다
• 데이터가 군집되어 있으면 IN-List 방식이 더 많은 I/O 를 반복적으로 발생시킬 수 있다.(리프 노드에는 많은 데이터가 존재)
• 3번이면 될거를 3번의 수직적 탐색을 통해 9번으로 뻥튀기 될 수도 있다
• NUM_INDEX_KEYS 를 통해 액세스 조건 또는 필터 조건으로 유도하는방법도 존재한다.(p207 ~ p208)

3.3.9 BETWEEN 과 LIKE 스캔 범위 비교

where A like '2019%'

where A between '201901' and '201912;

• Like 가 코딩하기 쉽기 때문에 개발자에게 더 선호된다
• 하지만 결론적으로 BETWEEN 이 Like 보다 무조건 효율적이다(손해보지 않는다)
• like 는 '201900' 의 가능성 과 '201913' 의 가능성을 배제할 수 없어 범위가 넓어진다

3.3.10 범위검색을 남용할 때 새익는 비효율

• 특점 컬럼은 입력하지 않을 수도 있고, 단어중 일부만 입력 할 수도 있다.
• 이를 Like 로 처리하면 스캔량이 갑자기 늘어나게 될 수 있다.
• 코딩의 효율성 때문에 Like 혹은 Between 을 활용하여 SQL 량을 줄일려고 많이 시도하지만 인덱스 스캔 효율을 고려하며 좋지 않을 수 있다.
  • 종목코드를 between 으로 조건절 처리시 입력값이 없으면 모든 데이터를 조회해야 할 수 있다.
• 대량의 테이블을 검색할 때는 주의하자

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

OR 조건 활용

• 옵티마이저에 의한 OR Expansion 쿼리 변환이 기본적으로 작동되지 않을 수 있다.

• 따라서 인덱스 선두 컬럼에 대한 옵션 조건에 OR 조건을 사용해서는 안된다.

• 인덱스 필터 조건의 경우 랜덤 액세스를 한 후 필터링 한다는 의미이므로 해당 옵션을 사용할 이유가 없다. 그냥 테이블 필터 조건으로만 사용하자

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

• 따라서 가급적 사용하지 말자

• 유일한 장점은 옵션 조건 컬럼이 NULL 허용 컬럼이더라도 결과집합을 보장한다는 것뿐이다.

Like/BETWEEN 조건 활용

• 필수 조건 컬럼을 인덱스 선두에 두고 액세스 조건으로 사용하면 좋은 성능이 가능하다
• 필수 조건의 변별력이 좋지 않은 경우에는 오히려 Table Full Scan 이 유라할 수 있다.
• 다음 을 고려하자
  • 1. 인덱스 선두 컬럼
    • 선두 컬럼을 LIKE/BETWEEN 처리는 금물이다
    • 만약 선두 컬럼을 입력하지 않으면 모든 데이터를 스캔하게 된다.
    • 따라서 변별력이 안좋더라도 선두 컬럼은 가급적 = 조건의 컬럼을 사용해야 한다. 그로 인한 비효율을 감수해야 한다.
  • 2. NULL 허용 컬럼
    • 실제 NULL 값이 입력되어 있으면 그 데이터는 결과 집합에서 누락된다.
  • 3. 숫자형 컬럼
    • LIKE 사용시 자동 형변환으로 인해 모든 데이터를 스캔할 수 있다.
  • 4. 가변 길이 컬럼
    • 기대하지 않은 데이터가 나올 수 있다
    • 조건절을 추가해라

union all 활용

• 특정 값 입력 유무에따라 위아래 SQL 중 어느 하나만 실행되게 하는 방식(p 220)
• 특정 값을 입력하든 안하든 인덱스를 탈 수 있으므로 인덱스를 가장 최적으로 사용할 수 있다.
• UNION ALL 방식은 옵션 조건 컬럼도 인덱스 액세스 조건으로 사용된다는 사실이 중요하다
• 유일한 단점은 SQL 코딩량이 길어진다.

NVL/DECODE 함수 활용

• union all 보다 단순하면서도 union all 과 같은 성능을 낸다
• 단점은 like 패턴처럼 null 허용 컬럼에 사용할 수 없다.
• 추가적으로 공부해보자

동적 쿼리로 처리 가능하다. 하지만 금융권은 허용하지 않는 곳이 많고, 바인드 변수를 잘 활용하는 것이 중요하다

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

PL/SQL 함수의 성능적 특성

• 매우 느리다
• native 쿼리로 해결하자