7. 인덱스 스캔 효율

1. 비교 연산자 종류와 컬럼 순서에 따른 인덱스 레코드의 군집성

테이블과 달리 인덱스 레코드는 '같은 값을 갖는' 레코드들이 항상 서로 군집해 있다.
만약 비교 연산자가 '=' 조건이 아닐 때는 인덱스 레코드도 서로 흩어진 상태일 수 있다.

• 선두 컬럼 : 인덱스 구성상 맨 앞쪽에 있는 컬럼

• 선행 컬럼 : 상대적으로 앞쪽에 놓인 컬럼

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

2. 인덱스 선행 컬럼이 등치조건이 아닐 때 발생하는 비효율

인덱스 컬럼 중 일부가 조건절에서 생략되거나 '=' 조건이 아니더라도, 그것이 뒤쪽 컬럼일 때 비효율은 없다.
반면, 인덱스 선행 컬럼이 조건절에 누락되거나 between, 부등호, like 같은 범위 검색 조건이 사용되면 인덱스를 스캔하는 단계에서 비효율이 발생한다.

비효율이 전혀 없다는 것은 n건을 찾기 위해 n+1 만큼만 스캔한 것을 말한다.
맨 마지막 스캔은 one-plus 스캔이므로 불가피하다.
인덱스 선행 컬럼이 모두 '=' 조건일 때 필요한 범위만 스캔하고 멈출 수 있는 것은, 조건을 만족하는 레코드가 모두 한데 모여 있기 때문이다.

3. BETWEEN 조건을 IN-List로 바꾸었을 때 인덱스 스캔 효율

인덱스 컬럼에 IN-List 조건절을 사용하면 Concatenation 방식의 실행계획이 나타나고, 이는 쿼리가 내부적으로 union all 방식으로 변환되고 나서 실행됐음을 의미한다.

IN-List 개수만큼 union all 브랜치가 생성되고, 각 브랜치마다 모든 컬럼을 '=' 조건으로 검색하기 때문에 앞서 선두 컬럼을 between 조건으로 비교할 때와 같은 비효율이 사라진다.

✅ between 조건을 In-List 조건으로 바꿀 때 주의사항

IN-List 조건으로 바꿀 때, IN-List 개수가 많지 않아야 한다는 것이다.
IN-List 개수가 많을 때는, 인덱스 수직 탐색이 여러 번 발생하기 때문이다.

인덱스 스캔 과정에서 선택되는 레코드들이 서로 멀리 떨어져 있을 때만 유용하다.

4. Index Skip Scan을 이용한 비효율 해소

인덱스 선두 컬럼이 누락됐을 때뿐만 아니라 부등호, between, Like 같은 범위검색 조건일 때도 Index Skip Scan이 유용하게 사용된다.
상황에 따라서는 '=' 조건 컬럼들을 인덱스 선두에 위치시킨 것만큼의 효과를 낸다.

5. 범위 검색 조건을 남용할 때 발생하는 비효율

인덱스 컬럼에 범위검색 조건을 남용하면 첫 번째 범위검색 조건에 의해 스캔 범위가 대부분 결정되며, 그 다음을 따르는 조건부터는 스캔 범위를 줄이는 데에 크게 기여하지 못하므로 성능상 불리해질 수 있다.

따라서 SQL을 작성할 때 주의가 요구되며, 인덱스 컬럼에 따라 비교 연산자를 신중하게 선택해야 한다.

6. 같은 컬럼에 두 개의 범위검색 조건 사용 시 주의사항

겹치는 조건절이 아닌 컬럼 조건절을 가공하여 인덱스 액세스 조건으로 사용되지 않도록 의도적으로 만들어준다.

✅ OR-Expansion 방법과 주의사항

use_concat 힌트를 사용하면 union all을 사용할 때보다 SQL 코딩량을 줄일 수 있다.
OR 조건에 대한 expansion이 일어나면 뒤쪽 조건절이 먼저 실행된다는 특징을 이용한 것이다.

주의할 점은 버전에 따라 실행되는 순서가 달라지다는 것이다.

• ~ 오라클 9i : 뒤쪽 조건 값을 먼저 실행

• 오라클 10g : 카디널리티가 낮은 쪽을 먼저 실행

=> ordered_predicates 힌트 : 값 분포와 상관없이 항상 뒤쪽에 있는 조건식 먼저 처리

rowid를 concatenation하면 내부적으로 rowid가 문자형으로 자동 형변환돼 비교할 때와 정렬순서가 다르다.

7. Between과 Like 스캔 범위 비교

like와 between은 둘 다 범위검색 조건으로서, 앞에서 설명한 범위검색 조건을 사용할 때의 비효율 원리도 똑같이 적용된다.
하지만 검색을 위해 둘 간의 인덱스 스캔량은 다를 수 있는데, between을 사용한다면 적어도 손해볼 일은 없다.

8. 선분이력의 인덱스 스캔 효율

✅ 선분이력이란?

고객의 변경이력을 관리할 때 이력의 시작시점만을 관리하는 것을 '점이력' 모델이라고 하고, 시작시점과 종료시점을 함께 관리하는 것을 '선분이력' 모델이라고 한다.

이력을 선분형태로 관리하면 무엇보다 쿼리가 간단해진다는 것이 가장 큰 장점이다.
대신 이력이 추가될 때마다 기존 최종 이력의 종료일자도 같이 변경해 주어야 하는 불편함이 있다. 이 때문에 DML 성능이 나빠지고 이력 데이터를 관리하는 프로그램이 복잡해진다.

또 한가지의 단점은, 개체 무결성을 사용자가 직접 관리해 주어야 한다는 것이다.
선분이력의 개체 무결성을 확보하려면 선분의 중복이 없어야 한다.

9. Access Predicate와 Filter Predicate

인덱스를 경유해 테이블을 액세스할 때는 최대 3가지 Predicate 정보가 나타난다.

1. 인덱스 단계에서의 Access Predicate

'인덱스 단계에서의 Access Predicate'는 인덱스 스캔 범위를 결정하는 데에 영향을 미치는 조건절을 의미한다.
인덱스 컬럼에 대한 조건절은, 설령 범위검색 조건을 뒤따르는 조건이거나 선행 컬럼이 조건절에서 누락돼도, 액세스 범위를 결정하는 데에 기여하므로 대부분 Access Predicate에 포함된다.

• 좌변 컬럼을 가공한 조건절

• 왼쪽 '%' 또는 양쪽 '%' 기호를 사용한 Like 조건절

• 같은 컬럼에 대한 조건절이 두 개 이상일 때, 인덱스 액세스 조건으로 선택되지 못한 조건절들

위의 경우를 제외하면 수직적 탐색 과정에서 모든 인덱스 컬럼을 비교 조건으로 사용한다.
물론 수평적 탐색 종료 지점을 결정하는 비교조건으로도 사용된다.

2. 인덱스 단계에서의 Filter Predicate

테이블로의 액세스 여부를 결정짓는 조건절을 의미한다.
첫 번째 나타나는 범위검색 조건부터 이후 모든 조건절 컬럼들이 여기에 포함되며, 조건절에서 누락된 컬럼에 놓인 인덱스 컬럼들도 포함한다.

3. 테이블 단계에서의 Filter Predicate

테이블을 액세스하고 나서 최종 결과집합으로의 포함여부를 결정짓는 조건절을 의미한다.
인덱스 컬럼에 없는 조건절 컬럼을 말한다.

---

인덱스를 경유하지 않고 테이블 전체를 스캔할 떄는 항상 테이블 단계에서의 Filter Predicate 단 한 가지만 나타난다.

10. Index Fragmentation

✅ Index Skew

인덱스 엔트리가 왼쪽 또는 오른쪽에 치우치는 현상을 말한다.
대량의 delete 작업 후 리프 블록의 한 방향이텅 빈 현상이다.
Index Skew 때문에 성능이 나빠지는 경우는 대게 Index Full Scan할 때이다.

대량의 데이터를 매일 지웠다가 새로 입력하는 통계성 테이블일 때는 Index Skew가 발생하지 않도록 트랜잭션 패턴에 신경을 써야 한다.

✅ Index Sparse

인덱스 블록 전반에 걸쳐 밀도가 떨어지는 현상을 말한다.
지워진 자리에 새로운 값이 입력되지 않으면 영영 재사용되지 않을 수도 있다.

✅ Index Rebuild

index fragmentation 해소 => coalesce 명령어 사용

-> 여러 인덱스 블록을 하나로 병합 후, 그 결과로 생긴 빈 블록들은 freelist에 반환한다.

index fragmentation 해소 + 공간까지 반환 => shrink 명령어 사용 (ASSM에만 작동)

위의 두 방법은 레코드를 건건이 지웠다가 다시 입력하는 방식이다.
작업량이 많을 때는 rebulid 명령어를 사용하는 것도 방법이다.

index rebuild 하면 저장 효율이나 스캔 효율은 좋아지지만 일반적으로 인덱스 블록에는 어느 정도 공간을 남겨두는 것이 좋다.
인덱스 블록에 공간이 없으면 인덱스 분할이 자주 발생해 DML 성능이 떨어지기 때문이다.
인덱스 분할에 의한 경합을 줄이려면 pctfree를 높이고 나서 index rebuild를 해야 한다.

Index Rebuild 상황

• 인덱스 분할에 의한 경합이 현저히 높을 때

• 자주 사용되는 인덱스 스캔 효율을 높이고자 할 때
  ->특히 NL조인에서 반복 액세스되는 인덱스 높이가 증가했을 때

• 대량의 delete 작업을 수행한 이후 다시 레코드가 입력되기까지 오랜 기간이 소요될 때

• 총 레코드 수가 일정한대도 인덱스가 계속 커질 때

---

인덱스 freelist

테이블의 pctfree와 pctused는 각각 freelist에서 제외되는 시점과 다시 등록되는 시점을 지정하는 파라미터 이다.
테이블은 Heap 방식으로 데이터를 입력하므로 매번 freelist를 참조해야 하지만, 인덱스는 정렬된 구조로 자료를 삽입하므로 매번 freelist를 참조하지 않고 인덱스 분할로 빈 블록이 필요할 때만 참조한다.

• PCTFREE

  테이블에서의 pctfree는 블록에 더 이상 insert가 발생하지 못하도록 freelist로부터 제외되는 시점을 지정하는 것이다.
  남겨진 공간은 나중에 update를 위해 사용된다.

  인덱스에서의 pctfree는 인덱스가 생성되는 시점에 공간을 꽉 채워두면 나중에 인덱스 분할이 빈번하게 발생하므로 이를 방지하기 위해 필요하다.
  이 옵션은 인덱스 최초 생성 또는 재생성 시점에만 적용되며, 남겨진 공간은 나중에 insert를 위해 사용된다.

• PCTUSED

  freelist에서 제거된 테이블 블록에 빈 공간이 일정 수준 이상 확보됐을 때만 다시 freelist에 등록되도록 하기 위해 pctused 파라미터가 필요하다.

  인덱스에서 빈공간은 항상 재사용 가능하기 때문에 pctused 파라미터가 없다.

  정리하자면, 테이블에서의 freelist는 insert가 가능한 블록을 관리하고, 인덱스에서의 freelist는 인덱스 분할에 사용 가능한 빈 블록들을 관리한다.

---

8. 인덱스 설계

1. 인덱스 설계 선택 기준

1. 조건절에 항상 사용되거나, 자주 등장하는 컬럼들을 선정한다.

2. '=' 조건으로 자주 조회되는 컬럼들을 앞쪽에 둔다.

2. 인덱스 선택 전략

• 쿼리 수행 빈도

• 업무상 중요도

• 클러스터링 팩터

• 데이터량

• DML 부하

• 저장 공간

• 인덱스 관리 비용 등

액세스 효율을 높이는 것도 중요하지만 인덱스 개수를 최소화하는 것도 중요하다.
이 둘의 조화를 이뤄야 한다.

3. 결합 인덱스 컬럼 순서 결정 시, 선택도 이슈

결합 인덱스 컬럼 간 순서를 정할 때 선택도가 낮은(=변별력이 높은) 컬럼을 앞에 두는 것이 유리할까?

=> 상황마다 다르다

✅ 선택도가 액세스 효율에 영향을 주지 않는 경우

먼저 인덱스 필터 조건은 변별력이 좋고 나쁨을 따질 필요가 없다.
액세스 조건에서의 '=' 조건절의 순서는 어디에 두든 상관없다.

선행 컬럼이 '=' 조건이면 첫 번째 범위 검색 조건까지의 인덱스 레코드들은 모두 한 곳에 모여 있기 때문에 변별력이 좋지 않은 컬럼을 앞에 두더라도 수평적 탐색을 위한 스캔 범위는 최소화될 것이고, 인덱스 액세스 효율에 전혀 영향을 미치지 않는다.

✅ 선택도가 '높은 컬럼'을 앞쪽에 두는 것이 유리한 경우

선택도가 높은 컬럼을 선두에 두면 나중에 범위검색 조건이 사용되거나 아예 조건절에서 누락되어도 Index Skip Scan 또는 IN-List 를 활용할 수 있어 유리하다.

선택도가 낮은 컬럼을 선두에 두면 이를 범위검색 조건으로 조회하는 일이 생겼을 때 불리하지만, 입력 값의 범위가 좁다면 비효율이 크지 않아 Index Skip Scan 이나 IN-List 를 활용하지 못하더라도 오히려 유리할 수 있다.

✅ 선택도가 '낮은 컬럼'을 앞쪽에 두는 것이 유리한 경우

범위검색 조건을 사이에 둔 컬럼끼리는 선택도가 낮은 컬럼을 앞쪽에 두는 것이 유리하다.
인덱스 액세스 조건에 선택도가 낮은 컬럼을 두라는 뜻이다.

✅ 선택도가 낮은 컬럼을 '선택' 하는 것이 유리한 경우

where 거래일자 = :a and 상품번호 = :b

where 거래일자 = :a and 고객번호 = :b

위 두 조건 중 하나의 인덱스를 만들어야 된다면 거래일자 뒤에 상품번호와 고객번호 중 어떤 컬럼을 선택해야 더 효율적일까?

만약 사용빈도까지 똑같다면 둘 중 선택도가 낮은 컬럼을 선택하는 것이 유리하다.
선택도가 낮은 컬럼을 인덱스 후행으로 선택해야 더 적은 양의 테이블 Random 액세스가 발생하기 때문이다.

하지만 웬만해서는 이 둘간의 선택도를 비교해서 인덱스 생성 여부를 결정하지는 않는다.
선택도가 낮으면 둘 다 생성할 수 있고, 선택도가 높으면 둘 다 생성하지 않을 수 있다.

결론적으로 결합 인덱스 컬럼 간 순서를 정할 때는, 개별 컬럼의 선택도보다는 조건절에서 어떤 형태로 자주 사용되는지, 사용빈도는 어떤 쪽이 높은지, 데이터를 빠르게 검색하는 데에 어느 쪽 효용성이 높은지 등이 더 중요한 판단기준이다.

4. 소트 오퍼레이션을 생략하기 위한 컬럼 추가

인덱스는 항상 정렬 상태를 유지하므로 order by, group by 를 위한 소트 연산을 생략할 수 있도록 해 준다.

인덱스를 이용해 소트 연산을 대체하려면, 인덱스 컬럼 구성과 같은 순서로 누락 없이
order by 절에 기술해 주어야 한다.
단, 인덱스 구성 컬럼이 조건절에서 '=' 연산자로 비교된다면, 그 컬럼은 order by 절에서 누락되거나 인덱스와 다른 순서로 기술하더라도 상관없다.
이 규칙은 group by 절에도 똑같이 적용된다.

---

9. 비트맵 인덱스

1. 비트맵 인덱스의 기본 구조

비트맵 인덱스는 키 값에 중복이 없고, 키 값별로 하나의 비트맵 레코드를 가진다.
그리고 비트맵 상의 각 비트가 하나의 테이블 레코드와 매핑된다.
비트가 1로 설정돼 있으면 상응하는 테이블 레코드가 해당 키 값을 포함하고 있음을 의미한다.

비트맵 인덱스는 첫 번째와 마지막 비트의 rowid만을 갖고 있다가 테이블 액세스가 필요할 때면 각 비트가 첫 번째 비트로부터 떨어져 있는 상대적인 거리를 이용해 rowid 값을 환산한다.

✅ 키 값의 수가 많을 때

비트맵 인덱스의 키 값이 많을 때는 오라클이 B*Tree 인덱스 구조를 사용하며, 값의 수가 많을 수록 인덱스 높이도 증가한다.
이런 구조라면 B*Tree 인덱스보다 더 많은 공간을 차지할 수 있어 비트맵 인덱스로 부적합하다.

2. 비트맵 인덱스 활용

비트맵 인덱스는 성별처럼 Distinct Value 개수가 적을 때 저장효율이 좋다.
그런 컬럼이라면 적응 용량을 차지하므로 인덱스가 여러 개 필요한 대용량 테이블에 유용하다.

하나의 비트맵 인덱스 단독으로는 쓰임새가 별로 없지만 여러 비트맵 인덱스를 동시에 사용할 수 있다는 특징 때문에 대용량 데이터 검색 성능을 향상시키는 데에 큰 효과를 발휘한다.

비트맵 인덱스는 여러 인덱스를 동시에 활용할 수 있다는 장점 때문에 다양한 조건절이 사용되지만, 특히 정형화되지 않은 임의 질의가 많은 환경에 적합하다.

비트맵 인덱스의 단점은 DML 부하가 심하다는 것이다.
레코드 하나만 변경하더라도 해당 비트맵 범위에 속한 모든 레코드에 lock이 걸리기 때문에 OLTP성 환경에서는 비트맵 인덱스를 쓸 수 없다.
이런 특징들 때문에 비트맵 인덱스는 읽기 위주의 대용량 DW/OLAP 환경에 적합하다.