잡동사니 보물섬

힌트를 이용해 NL 조인을 제어하는 방법 

 - use_nl() 

 - 10g 부터 leading 힌트에 2개 이상 테이블을 기술할 수 있도록 기능이 개선 

NL 조인 수행 과정 분석 

 - NL 조인은 각 단계를 완료하고 나서 다음 단계로 넘어가는 게 아니라 한 레코드씩 순차적으로 

   진행한다는 사실이다. 

   ( 단, order by는 전체 집합을 대상으로 정렬해야 하므로 작업을 모두 완료한 후에 

     다음 오퍼레이션을 진행한다. )

 - NL 조인은 outer 테이블과 inner 테이블과 인덱스 사용으로 인한 random access가 

   1차 적이 부하 지점이 된다. 

 - OLTP 시스템에서 조인을 튜닝할 때는 일차적으로 NL 조인부터 고려하는 것이 올바른 순서다. 

NL 조인의 특징 

 - Random 엑세스 위주의 조인 

 - 조인을 한 레코드씩 순차적으로 진행 

 - 인덱스 구성 전략이 특히 중요하다. 

 - 소량의 데이터를 주로 처리하거나 부분범위 처리가 가능한 온라인 트랜잭션 환경에 적합한 

   조인 방식 

테이블 Prefetch 

 - 인덱스 rowid에 의한 inner 테이블 엑세스가 Nested loops 위쪽에 표시 되는 것은 

   테이블 엑세스 단계에서 Prefetch 기능이 적용되었음을 표현하기 위함 

 - Prefetch 기능이 실제로 작동할 때면 db file sequential read 대기 이벤트 대신 

   db file parallel reads 대기 이벤트가 나타난다. 

 - 새 포켓의 실행계획이 나타날 수 있는 경우 

     ● Inner 쪽 Non-Unique 인덱스를 Range Scan 할 때는 테이블 Prefetch 항상 나타남 

     ● Inner 쪽 Unique 인덱스를 Non-Unique 조건으로

         (모든 인덱스 구성컬럼이 '=' 조건이 아닐때) Range Scan 할 때 항상 나타남 

     ● Inner 쪽 Unique 인덱스를 Unique 조건으로 엑세스 할 때도 테이블 prefetch 실행계획이 

         나타날 수 있다. 이때 인덱스는 Range Scan으로 엑세스 한다. 

         테이블 Prefetch 실행계획이 안 나타날 때는 Unique Scan으로 엑세스한다. 

 - 테이블 Prefetch는 디스크 I/O에 의한 대기 횟수를 감소시킨다. 

 - 이 기능은 인덱스 클러스터링 팩터가 나쁠 때 특히 효과를 발휘한다. 

   ( 클러스터링 팩터가 나쁘면 논리적 I/O가 증가할 뿐 아니라 디스크 I/O도 많이 발생하기 때문 )

 * 이 부분은 나의 생각 

   - 결국 테이블 Prefetch인덱스를 통한 중간 결과 값을 만들고 rowid 순으로 정렬하여서 

     필요한 block들을 여러 개 빠르게 읽어 들이는 것인가? 

배치 I/O 

 - Inner 쪽 인덱스만으로 조인을 하고 나서 테이블과의 조인은 나중에 일괄 처리하는 

   매커니즘인 것으로 추정된다. 

 - 인덱스와의 조인을 모두 완료하고 나서 테이블 엑세스하는 것이 아니라 일정량씩 나누어 

   처리하는 것 

 - 이 메커니즘이 작동하도록 유도하려면 nlj_batching 힌트를 사용하면 된다. 

   이 방식을 원치 않을 때는 no_nlj_batching 또는 nlj_prefetch 힌트를 사용하면된다. 

    (그럼 Prefetch 방식으로 전환된다.) 

 - 이 방식을 사용할 때 inner쪽 테이블 블록이 모두 버퍼 캐시에서 찾아지지않으면 

   데이터 정렬 순서가 달라질 수 있다는 사실이다. 

 - 테이블 Prefetch 방식이나 전통적인 방식으로 NL 조인할 때는 디스크 I/O가 발생하든 안하든 

   데이터 정렬 순서가 항상 일정하다. 

  * 내 생각 

    - 결국 테이블 Prefetch 나 배치 I/O 둘 중 하나만 사용해야 하는 것인가? 

버퍼 Pinning 효과 

 - 8i에서 나타난 버퍼 Pinning 효과 

    ● 단, 하나의 버퍼 블록만 Pinning 그리고 하나의 Fetch Call을 완료하는 순간 Pin을 해제 

     (따라서, Inner 쪽 인덱스를 통해 엑세스 되는 테이블 블록이 계속 같은 블록을 가리키면 

      논리 I/O가 추가로 발생하지 않는다.  )

     ● 중요한 사실은 inner 쪽과의 조인을 마치고 다른 레코드를 읽기 위해 

         outer 쪽으로 돌아오는 순간 Pin을 해제 

         (그래서 클러스터링 팩터가 좋아야 그 효과가 확실) 

     ● NL 조인에서도 하나의 Fetch Call을 완료하면 Pin을 해체 

 - 9i에서 버퍼 Pinning 효과 

     ● inner 쪽 인덱스 루트 블록에 대한 버퍼 Pinning 효과가나타나기 시작 

         (단, 두번째 엑세스 되는 순간 Pinning) 

     ● 9i 부터 Inner 쪽이 Non-Unique 인덱스일 때는 테이블 엑세스가 항상 NL 조인 위쪽으로 

         올라가므로 이때는 항상 버퍼 Pinning 효과가 나타나는 셈 

         ( 테이블 엑세스가 NL조인 위쪽으로 올라가지 않을 때는 

           버퍼 Pinning 효과가 나타날 수 없음 )

 - 10g에서 버퍼 Pinning 효과 

     ● Inner 쪽 테이블을 Index Range Scan을 거쳐 NL조인 위쪽에서 엑세스 할 때는, 

         하나의 Outer 레코드에 대한 Inner 쪽과의 조인을 마치고 Outer 쪽으로 돌아오더라도 

         테이블 블록에 대한 Pinning 상태를 유지한다. 

     ● 버퍼 Pinning 효과는 하나의 데이터베이스 Call 내에세만 유료하다. 

         (여러번의 Fetch Call이 발생 시 Pin을 해제 했다가 다시 Pin을 걸어야 하기 때문에 

          약간의 블록 I/O가 추가로 발생한다.

     ● Non-Unique 인덱스로 조인한다면, 테이블 엑세스가 NL 위쪽에 있던 아래쪽에 있든 

         8i 이후 모든 버전에서 모두 버퍼 Pinning 효과가 나타난다. 

         (물론 10에서 I/O가 좀 더 줄 가능성이 있다. )

 - 11g에서 나타난 버퍼 Pinning 효과 

     ● User Rowid로 테이블 엑세스 할 때( 일반적인 filter로 )도 버퍼 Pinning 효과

     ● NL조인에서 Inner 쪽 루트 아래 인덱스 블록도 Pinning 

     ● 인덱스 블록 버퍼 Pinning 효과는 배치 I/O 실행계획과 상관없이 나타난다.  

     ● Use_nl_with_index() 힌트는 nl 조인시 인덱스와 조인하라는 힌트 

     ● 집계 함수를 쓰지 않고 결과집합을 출력하는 쿼리라면, 드라이빙 집합을 

          Inner쪽 인덱스 컬럼 순으로 정렬하고 나서 NL 조인함으로써 블록 I/O를 줄임

비트맵 인덱스 

 - 비트맵 인덱스는 키 값에 중복이 없고, 키 값별로 하나의 비트맵 레코드를 갖는다. 

   비트맵 상의 각 비트가 하나의 테이블 레코드와 매핑된다. 

   비트가 1로 설정돼 있으면 상응하는 테이블 레코드가 해당 키 값을 포함하고 있음을 의미한다. 

 - 비트맵 인덱스는 첫 번째오 마지막 비트의 rowid만을 갖고 있다가 

   테이블 엑세스가 필요할 때면 각 비트가 첫 번째 비트로부터 떨어져 있는 상대적인 거리를 

   이용해 rowid 값을 환산한다. 

비트맵 위치와 rowid 매핑 

 - 오라클은 한 블록에 저장할 수 있는 최대 레코드 개수를 제한하고 이 특징을 이용해 

   비트맵 위치와 rowid를 매핑한다. 

키 값의 수가 많을 때 

 - 비트맵 인덱스는 키 값별로 하나의 레코드를 갖는데, 저장할 키 값의 수가 아주 많을 때는 

   한 블록에 모두 담지 못한다. 비트맵을 저장하기 위해 두 개 이상 블록이 필요해지면 

   비트리 인덱스 구조를 사용하며, 값의 수가 많을 수록 인덱스 높이도 증가한다. 

 - 이런 구조라면 비트리 인덱스보다 더 많은 공간을 차지할 수 있어 비트맵 인덱스로 부적합

키 값별로 로우 수가 많을 때 

 - 한 블록에 적어도 2개의 비트맵 레코드가 담기도록 잘라서 저장한다. 

비트맵 압축 

 - 실제로는 여러 가지 압축 알고리즘이 사용되기 때문에 서로 다른 rowid 범위를 갖는다. 

 - 완전히 0으로 채워진 비트맵 블록들을 제거하고, 비트맵 뒤쪽에 0이 반복되어도 이를 제거 

   앞, 뒤, 중간 어디든 같은 비트맵 문자열이 반복되면 checksum 비트를 두어 압축한다. 

 - 이 때문에 각 비트맵이 가리키는 rowid 구간이 서로 달라지지만 시작 rowid와 종료 rowid만 

   알고 있으면 비트와 매핑되는 rowid를 계산하거나 다른 비트맵 과 Bitwise 연산하는 데에는 

   전혀 지장이 없다. 

비트맵 인덱스 활용 

 - Distinct value 개수가 적을 때 저장효율이 좋다. 그런 컬럼이라면 비트리 인덱스보다 훨씬 

   적은 용량을 차지하므로 인덱스가 여러 개 필요한 대용량 테이블에 유용하다. 

   주로 다양한 분석관점을 가진 팩트성 테이블이 여기에 속한다. 

   (반대로 distinct value가 아주 많은 컬럼이면 오히려 비트리 인덱스보다 많은 공간을 차지한다.) 

 - Distinct Value 개수가 적은 컬럼일때 저장효율이 좋지만 테이블 Random 엑세스 발생 측면

   에서는 비트리 인덱스와 똑같기 때문에 그런 컬럼을 비트맵 인덱스로 검새하면 그다지 좋은 

   성능을 기대하기 어렵다. 스캔할 블록이 줄어드는 정도의 성능 이점만 얻을 수 있다. 

 - 하나의 비트맵 인덱스 단독으로는 쓰임새가 별로 없지만 여러 비트맵 인덱스를 동시에 

   사용할 수 있다느 특징 때문에 대용량 데이터 검색 성능을 향상시키는 데에 큰 효과를 발휘 

 - 비트맵 인덱스를 이용하면 null 값에 대한 검색도 가능하다. 

 - 비트맵 인덱스는 여러 인덱스를 동시에 활용할 수 있다는 장점 때문에 다양한 조건절이 

   사용되는, 특히 정형화되지 않은 임의 질의가 많은 환경에 적합하다. 

 - 다만, 비트맵 인덱스는 lock에 의한 DML 부하가 심한 것이 단점이다.

   레코드 하나만 변경되더라도 해당 비트맵 범위에 속한 모든 레코드에 lock이 걸린다. 

   (이래서 OLTP 성 환경에서 비트맵 인덱스를 사용하기 힘들다.) 

 - 이러한 특징으로 비트맵 인덱스는 읽기 위주로 대용량 환경에 적합하다. 

인덱스 설계에서 가장 중요한 두 가지 선택 기준 

 - 조건절에 하상 사용되거나, 자주 등장하는 컬럼 

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

스캔 효율성 이외의 판단 기준 

  - 쿼리 수행 빈도 

  - NL 조인의 Inner 쪽 테이블로서 자주 엑세스되는지도 중요한 판단 기준 

  - 업무상 중요도 

  - 클러스터링 팩터 

  - 데이터량 

  - DML 부하 (=기존 인덱스 개수, 초당 DML 발생량, 자주 갱신되는 컬럼 포함 여부)

  - 저장 공간 

  - 인덱스 관리 비용 

  - ( 실무적으로는 파티션 설계를 먼저 진행하거나 

     최소한 인덱스 설계와 병행하는 것이 바람직하다. )

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

  - '=' 조건으로 항상 사용되는 컬럼들을 앞쪽에 위치시켰다면, 그 중 선택도가 낮은 것을 

   앞쪽에 두려는 노력은 의미 없는 것이거나 오히려 손해일 수 있다. 

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

  - 선택도가 높은 컬럼을 앞에 두는 것은 9i 부터 제공되는 Index Skip Scan을 

     활용할 수 있어 유리하다. 

  - index skip scan이 아니더라도 In-list로 값을 제공함으로써 쉽게 튜닝할 여지가 생긴다. 

  - 인덱스 압축 기능을 고려하더라도 선택도가 높은 컬럼을 앞쪽에 두는 것이 유리하다. 

    ( 선택도가 높은 컬럼을 앞쪽에 두어야 인덱스 압축률이 더 좋아지기 때문이다. )

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

  - 범위검색 조건을 사이에 둔 컬럼끼리는 선택도가 낮은 컬럼을 앞쪽에 두는 것이 유리하다

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

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

  - 인덱스를 이용해 소트 연산을 대체하려면, 인덱스 컬럼 구성과 같은 순서로 누락 없이 

    order by절에 기술해 주어야 한다. 

    단, 인덱스 구성 컬럼이 조건절에서 '=' 연산자로 비교된다면, 그 컬럼은 order by  절에서

    누락되거나 인덱스와 다른 순서로 기술하더라도 상관 없다. 

    ( 이런 규칙은 group by 절에도 똑같이 적용된다. ) 

  - 통계정보를 기반으로 비용을 계산한 결과 옵티마이저가 Table Full Scan을 선택하거나 

    다른 인덱스를 선택한다면 정렬 작업은 별로도 수행된다. 

  - 인덱스 컬럼중 하나라도 범위 조건으로 기술 되면 order by는 순서대로 기술해야 한다.