성능고도화 1-7. 인덱스 스캔 효율

I/O 튜닝의 핵심 원리 

  - Sequential 엑세스의 선택도를 높인다. 

  - Random 엑세스 발생량을 줄인다. 

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

  - 선행 컬림이 모두 '=' 조건인 상태에서는 첫 번째 나타나느 범위검색 조건까지만 만족하는 

    인덱스 레코드는 모두 연속되게 모여 있지만, 그 이하 조건까지 만족하는 레코드는 

    비교 연산자 종류에 상관없이 흩어진다.  

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

 - 인덱스 컬럼 중 일부가 조건절에서 생략되거나 '=' 조건이 아니더라도 그것이 뒤쪽 컬럼일 때는 

   비효율이 없다. 

   (반면, 인덱스 선행 컬럼이 조건절에 누락되거나 between, 부등호, like 같은 

    범위검색 조건이 사용되면 인덱스를 스캔하는 단계에서 비효율이 발생한다. ) 

 

Between 조건을 In-List로 바꾸었을 때 인덱스 스캔 효율 

 - In-List는 쿼리 내부적으로 Union all 방식으로 변환 후 실행 

   ( Index Skip Scan 방식으로 유도해도 비슷한 효율을 얻을 수 있으나 실행 방식은 다름)

Between 조건을 IN-List 조건으로 바꿀 시 주의 사항 

 - In-List 개수가 많지 않아야 한다. 

   (인덱스 수직 탐색이 여러번 발생함) 

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

   (인덱스 리브 블록에는 테이블 블록과 달리 매우 많은 레코드가 담긴다.) 

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

 - 선택도가 낮은 조건으로 인덱스가 사용되도록 선택도나 높은 것은 조건절 변형을 함 

OR-Expansion을 이용하는 방법과 주의 사항

 - use_concat 힌트는 or-expansion이 나타나도록 하는 힌트 

 - 9i 까지는 뒤쪽에 있는 조건 값을 먼저 실행 

   , 10g CPU 비용 모델에서는 계산된 카디널리티가 낮은 쪽을 먼저 실행 

   (10g에서 상관없이 항상 뒤쪽에 있는 조건식이 먼저 처리 되로독하라며녀 

    ordered_predicates 힌트를 명시해야 한다. )

 

rowid를 concatenation 하면 결과에 오류 발생 

 - 문자형으로 변환된 rowid는 rowid 값 그대로 비교할 때와 정렬순서가 다르다. 

인덱스를 스캔하면서 rowid를 필터링할 때 발생하는 비효율 

 - rowid를 가지고 '=' 조건으로 바로 엑세스 할 땐 어떤 엑세스보다 빠르지만 

   인덱스를 스캔하면서 rowid를 필터링할 때는 아니다. 

 - 인덱스 rowid는 리프 블록에만 있기 때문에 이를 필터링하려면 일단 다른 엑세스 조건만으로 

   리프 블록을 찾아가야 한다. 

Between과 like 스캔 범위 비교 

 - between을 사용한다면 적어도 손해볼 일은 없다. 

   (between 사용시 해당 범위를 읽을 때 뒤의 다른 조건이 있을 시 그때부터 인덱스를 읽어간다.)

 - between 이 like 보다 더 넓은 범위를 스캔하는 겨우는 없으므로 가급적 between을 사용

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

 - 선분이력의 특성상 두 번째 부등호 조건이 스캔 범위를 줄이는 데 전혀 도움을 주지 못한다. 

 - 시작시점과 종료시점을 함께 관리하는 것을 '선분이력' 모델이라고 한다. 

 - 이력을 선분형태로 고나리하면 쿼리가 간단해진다. 

 - 쿼리가 간단하지만 성능상 유리할 때가 많지만, 이 때문에 DML 성능이 나빠질 뿐만아니라 

    이력 데이터를 관리하는 프로그램이 복잡해짐 

 - 또 하나의 단점은 개체 무결성을 사용자가 직접 관리해 주어야 한다. 

   (개체 무결성을 확보하려면 선분의 중복이 없어야 한다.) 

 - 종료일시 '99991231' 과 시작일시 '0시'로 하는 이유 

선분이력 효율적인 조회 방법 

 - [시작일+종료일] 구성일 때 최근 시점 조회 

     ● index_desc 힌트 & rownum <= 1 

 - [시작일+종료일] 구성일 때 과거 시점 조회 

     ● index_desc 힌트 & rownum <= 1 

 - [종료일+시작일]구성일 때 최근 시점 조회 

     ● rownum <= 1

 - [종료일+시작일]구성일 때 과서 시점 조회 

     ● rownum <= 1

 - 중간시점 

     ● rownum <= 1

 - 미래 시점 데이터를 미리 입력하는 경우가 없다면, 현재 시점 데이터 조회 시 

   종료일 ='99991231' 조건을 사용하는 것이 효과적

Access Predicate 와 Filter Predicate 

 - 인덱스 사용시 

     ● 인덱스 단계에서의 Access Predicate

            - 인덱스 Access Predicate가 안되는 경우 

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

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

                  ○ 같은 컬럼에 대한 조건절이 두 개 이상일 때, 인덱스 엑세스 조건으로  

                      선택되지 못한 다른 조건절

     ● 인덱스 단계에서의 Filter Predicate

            - 첫 번째 나타나는 범위검색 조건부터 이후 모든 조건절 컬럼들 

            - 조건절에서 누락된 컬럼 뒤쪽에 놓인 인덱스 컬럼들 

     ● 테이블 단계에서의 Filter Predicate

 - 인덱스를 사용하지 않고 테이블 스캔시

     ● 테이블 단계에서의 Filter Predicate 

            - 테이블 엑세스 후 최종 결과집합 포함여부를 결정짓는 조건절 

Index Fragmentation 

 - 오라클에서는 루트로부터 모든 리프 블록까지의 높이가 동일하다. 

 - 불균형은 생길 수 없지만 Index Fragmentation에 의한 Index Skew 또는 Sparse 현상은 

    종종 발생할 수 있고 인덱스 스캔 효율에 나쁜 영향을 미칠 수 있다. 

Index Skew 

 - 인덱스 엔트리가 왼쪽 또는 오른쪽에 치우치는 현상 

    ( 레코드가 모두 삭제된 블록은 언제든 재사용 가능하지만,

      다시 채워질때 까지 인덱스 스캔 효율이 낮아짐 ) 

 - index skew 때문에 성능이 나빠지는 경우는 대게 index full scan 할 때이다. 

Index Sparse 

 - 인덱스 블록 전반에 걸쳐 밀도가 떨어지는 현상 

 - Index Skew는 블록이 텅비면 곧바로 freelist로 변환돼 언제든 재사용되지만, 

   index sparse는 지워진 자리에 새로운 값이 입력되지 않으면 영영 재사용되지 않을 수도 있다. 

   ( 총 레코드 건수가 일정한데도 인덱스 공간 사용량이 계속 커지는 것은 

      대게 이런 현상에 기인한다.  )

Index rebuild 

  - rebuild 관련 명령어 

      ● coalesce 명령어 : 여러 인덱스 블록을 하나로 병합하고, 

                              그 결과로서 생긴 빈 블록들은 freelist에 반환한다. 

                              ( compact 옵션과 같다 공간은 반환하지 않으므로) 

      ● shrink 명령어 : index fragmentation을 해소하면서 공간까지 반환하는 명령어  

                           ( 단, shrink는 ASSM에서만 작동한다. )

      ● rebuild 명령어 : coalesce나 shrink는 레코드를 건건이 지웠다가 다시 입력하는 방식을 

                                사용 작업량을 많을 때는 rebuild 명렁을 사용하는 편이 나을 수 있다.

   

  - 인덱스 블로겡는 어느 정도 공간을 남겨두는 것이 좋다. 인덱스 블록에 공간이 전혀 없으면 

    인덱스 분할이 자주 발생해 DML 성능을 떨어뜨리기 때문 

    ( 인덱스 분할에 의한 경합을 줄이려면 pctfree를 높이고 인덱스 rebuild 해야 한다. )

  - 인덱스 pctfree는 인덱스를 처음 생성하거나 rebuild할 때만 적용된다. 

     ( 인덱스 분할 시 대기 이벤트 enq:TX - index contention ) 

  - 인덱스 rebuild 시 기대할 수 있는 예상 효과 

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

     ● 자주 사용되는 인덱스 스캔 효율을 높이고자 할 때, 

         특히 NL 조인에서 반복 엑세스 되는 인덱스 높이가 증가했을 때 

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

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

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

 - 인덱스는 delete에 의해 비워진 인덱스 블록은 커밋 시점에 freelist에 반환되지만 insert 시점에 

   다시 값이 입력되더라도 곧바로 freelist체서 제거되지 않는다. 

   (즉, freelist에서 얻는 블록이 비어있지 않으면 다른 블록을 재요청하기 전에 일단 해당 블록을 

    freelist에서 제거하는 방식이며, 이는 커밋 시점에수행해야 할 일량을 최소화하기 위함이다. )