잡동사니 보물섬

기본 매커니즘 

 - sort merge 조인은 실제 조인 오퍼레이션 과정은 NL 조인과 다르지 않다. 

   NL 조인과 마찬가지로 outer 조인할 때 순서가 고정된다. 

 - Sort Area는 PGA 영역에 할당되므로 SGA를 경유해 인덱스와 테이블을 엑세스할 때보다

   훨씬 빠르다. ( PGA 사용시 래치 획득 과정이 없다. ) 

 - use_merge 힌트를 사용한다. 

소트 머지 조인의 특징 

 - 소트 머지 조인은 조인을 위해 실시간으로 인덱스를 생성하는 것과 다름없다. 

   ( 그래서 조인 컬럼에 인덱스가 없는 상황에서 두 테이블을 독립적으로 읽어 

     조인 대상을 줄일 수 있을 때 유리 ) 

 - 스캔 위주의 엑세스 방식을 사용한다는 점도 소트 머지 조인의 중요한 특징 

 - 하지만, 양쪽 집합에서 정렬 대상 레코드를 찾는 작업만큼은 인덱스를 이용해 random access

   ( 그때 발생하는 random 엑세스랴잉 많다면 소트 머지 조인의 이점이 사라질 수 있음 )

소트 머지 조인이 유용할 때 

 - First 테이블에 소트 연산을 대체할 인덱스가 있을 때 

 - 조인할 First 집합이 이미 정렬돼 있을 때 

 - 조인 조건식이 등치(=) 조건이 아닐 때 

First 테이블에 소트 연산을 대체할 인덱스가 있을 때 

 - 소트 머지 조인은 한쪽 집합은 전체 범위를 처리하고 다른 한쪽은 일부만 읽고 멈추도록 

   할 수 있다. First 테이블 조인 컬럼에 인데스가 있을 때 그렇다. 

    ( sort 연산 생략은 실행계획에 sort join이 하나 밖에 나타나지 않는다. )

 - Second 테이블 조인 컬럼에 대한 인덱스를 이용함에도 Sort Join 오퍼레이션이 나타나지만 

   이때는 소트 연산에 의한 부하가 크지 않다. 

 - 또, 항상 first 테이블을 먼저 읽는 것은 아니다. Second 테이블을 읽어 정렬한 결과를 

   Sort Area에 담는다. 조인 연산을 진행할 때는 first 인덱스 부터 읽기 시작한다. 

소트 머지 조인에서의 부분범위 처리 활용 

 - Second 테이블은 항상 정렬을 수행하므로 전체범위처리가 불가피하지만 First 테이블만큼은 

   중간에 읽다가 멈출 수 있다는 뜻이다. 

   ( 여기서 second 테이블의 전체범위처리가 불가피하다라는 것이 항상 헷갈렸는데 여러번 

     읽고 생각한 것은 second 테이블은 인덱스를 읽어서 부분집합을 만들게 되면 결국은 

     그 부분집합은 다 읽어질 수 밖에 없다. 아마도 그러한 부분을 전체범위처리가 불가피하다 

     라고 표현하지 않았나 싶다. ) 

조인할 First 집합이 이미 정렬 돼 있을 때 

 - 우선, 조인할 First 집합이 조인 컬럼 기준으로 이미 정렬된 상태는 in-line view 에서

    in-line view 안에 group by , order by, distinct  연산을 먼저 수행한 경우, 

    그때는 조인을 위해 다시 정열하지 않아도 되므로 소트 머지 조인이 유리하다. 

 - outer 테이블을 group by 하고서도 sort join 오퍼레이션이 나타날 수 있는데 

   그것은 hash group by로 처리했기 때문이다. 

   이때는 order by를 추가해주면 실제 정렬작업 없이 sort join을 생략할 수 있다. 

 - Second 집합에서는 order by를 추가하더라도 sort join이 발생한다. 

조인 조건식이 등치(=) 조건이 아닐 때 

 - 해시 조인은 조인 조건식이 등치(=) 조건일 때만 사용할 수 있다. 

힌트를 이용해 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는 순서대로 기술해야 한다. 

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에서 제거하는 방식이며, 이는 커밋 시점에수행해야 할 일량을 최소화하기 위함이다. ) 

IOT 

 - 인덱스 리프 블록이 곧 데이터 블록 

 - 오라클 IOT는 PK 컬럼 순으로만 정려할 수 있다. 

IOT 장단점 

 - IOT 장점 

   ● 인위적으로 클러스터링 팩터를 좋게 만드는 방법 중 하나 

   ● random 엑세스가 아닌 Sequential 방식으로 데이터를 엑세스 

   ● 넓은 범위를 엑세스 할 때 유리 

   ● PK 인덱스를 위한 별도의 세그먼트를 생성하지 않아도 됨 

  - IOT 단점 

   ● 데이터 입력시 느리다. ( 그러나, PK 인덱스를 두고 비교해 보면 차이가 거의 없다.) 

   ● 인덱스 분할 발생량 차이로 인한 성능차이가 크다. 

   ● Direct Path Insert 가 작동하지 않음 

IOT 사용 시기 

  - 크기가 작고 NL 조인으로 반복 룩업하는 테이블 

    (그러나, PK이외의 속성 크기 때문에 인덱스 높이가 증가한다면 역효과) 

  - 폭이 좁고 긴 테이블 

    (M:M 관계를 해소하기 위한 테이블) 

  - 넓은 범위를 주로 검색하는 테이블 

   ● Between, Like 같은 조건으로 넓은 범위를 검색하는 테이블 

       ( PK 이외 컬럼이 별로 없는 통계성 테이블에는 최적의 솔루션 ) 

       ( PK 구성 컬럼이 많은 통계성 테이블이 분석 관점과 엑세스 경로가 아주 다양할 시 

         BTree 결합 인덱스를 계속 추가는 저장공간이나 DML 부하 측면에 문제가 많아

         그럴 때 테이블을 IOT로 구성하면 효과적 )  

  - 데이터 입력과 조회 패턴이 서로 다른 테이블 

IOT 테이블의 정렬 순서 

  - 자주 사용하는 등치 조건을 선두에 구성하여 생성 

파티션 IOT

  - 파티션 IOT 구성시 컬럼 순서 중요 

Overflow 

  - PK 이외 컬럼이 많은 테이블일수록 인덱스 분할에 의한 DML 부하는 물론, 

    검색을 위한 스캔량도 늘어나기 때문이다. 

     (인덱스 분할에 의한 DML 부하는 인덱스 분할의 부하로 인하여 발생하는가 ?)  

  - 이 부분의 영역은 값은 저장해 두지만 출력이나 조회조건으로 거의 사용되지 않는다. 

  - 오라클은 PCTthreshold 또는 Including 둘 중 하나를 만족하는 컬럼을 Overflow 영역에 저장

  - overflow 영역을 읽을 때도 건건이 Random 엑세스가 발생한다.

  - overflow 영역에도 buffer pinning 효과가 나타난다. 

Secondary 인덱스 

 - 결론은 IOT는 Secondary 인덱스 추가 가능성이 크지 않을 때만 선택하는 것이 바람직하다. 

 - IOT Secondary 인덱스 

   ● logical rowid = PK + Physical Guess

   ● PCT_Direct_Access 가 100 일 때 physical Guess를 사용할 수 있다.  

 - 오라클은 secondary 인덱스의 Logical Rowid가 인덱스 키와 중복되면 이를 제거하고 

    그 밖의 컬럼만 저장한다.

인덱스 클러스터 테이블 

 - 클러스터 키 값이 같은 레코드가 한 블록에 모이도록 저장하는 구조

   한 블록에 모두 담을 수 없을 때는 새로운 블록을 할당해 클러스터 체인으로 연결

   ( 여러 테이블 레코드가 물리적으로 같이 저장될 수도 있다.)

 - 클러스터 테이블을 생성하려면 클러스터와 클러스터 인덱스가 필요하다 

 - 클러스터 인덱스의 키 값은 항상 Unique하며 1:M 관계를 갖는다. 

    이런 특성 때문에 Random 엑세스가 값 하나당 한 번씩 밖에 발생하지 않는다.

 - 클러스터 인덱스는 클러스터를 공유하는 테이블들이 공유해서 사용한다. 

 - 클러스터 테이블은 넓은 범위를 검색할 때 유용하다

인덱스 클러스터 테이블 종류

 - 단일 테이블 인덱스 클러스터 

 - 다중 테이블 인덱스 클러스터

클러스터 테이블과 관려된 성능 이슈

 - DML 성능이 다소 떨어지고 전에 없던 값을 입력할 때는 새로운 블록 할당 받아야 하기 때문에 

   더 느리다. 

    ( 클러스터를 만들지 않고 인덱스를 생성했을 경우는 비슷하다. 

      클러스터로 인하여 기존 인덱스를 두세게 없앨 수 있다면 DML부하가 줄 수있다. 

           - 아직 테스트 못해봄 )

 - data 삭제시 

    ● truc 사용할 수 없음

    ● drop 발생시에도 내부적으로 건건이 delete 작업 발생 

    ● 클러스터 자체를 지우는 것이 가장 빠르지만 클러스터를 지우면 

        클러스터링 된 테이블 모두 삭제 됨

DML 부하 외에 클러스터 테이블 관련한 성능 이슈 

 - Direct Path Loading을 수행할 수 없다. 

 - 파티셔닝 기능을 함께 적용할 수 없다. IOT의 경우는 Partitioned IOT가 가능하다. 

 - 다중 테이블 클러스터를 Full Scan 할 때는 다른 테이블 데이터까지 스캔하기 때문에 불리하다.

SIZE 옵션 

 - 하나의 블록에 담을 최대 클러스터 키 개수를 결정 

 - SIZE 옵션은 공간을 미리 예약해 두는 것일 뿐 그 크기를 초과했다고 

   값을 저장하지 못하도록 하지 않는다.

 - SIZE 옵션 때문에 데이터 입력이 방해 받지 않지만 대부분 클러스터 키 값이 한 블록씩을 

   초과한다면 굳이 이 옵션을 두어 클러스터 체인이 발생하도록 할 이유는 없다. 

해시 클러스터 테이블   

 - 해시 함수에 반환된 값이 같은 데이터를 물리적으로 함께 저장하는 구조 

 - 해싱 알고리즘을 이용해 클러스터 키 값을 데이터 블록 주소로 변환 

 - 가장 큰 제약은 등치조건 '=' 검색만 가능하다. 

 - 물리적인 인덱스를 따로 갖지 않기 때문에 해시 클러스터키로 검색할 때는 그만큼 블록I/O가 

   덜 발생한다는 이점이 생긴다. 

인덱스 컬럼 추가 

 - 인덱스 순서를 바꿀 수 없을 시 인덱스 컬럼추가 하여 인덱스 스캔량은 같지만 

   테이블 random access 양을 줄인다. 

PK 인덱스에 컬럼추가 

 - 인덱스의 개수를 줄이기 위해서 사용 

 - non_unique 인덱스를 만들고 해당 인덱스의 컬럼으로 PK 인덱스 생성 

 - PK 제약을 위한 컬럼들이 non-unique 인덱스에 선두에 와야함 

   (순서는 상관 없음) 

컬럼 추가에 따른 클러스터링 팩터 

 - 인덱스에 컬럼을 추가함으로써 테이블 Random 엑세 부하를 줄이는 효과가 있다. 

 - 그러나 인덱스 클러스터링 팩터가 나빠지는 부작용을 초래할 수도 있다. 

인덱스만 읽고 처리 

 - 인덱스 컬럼이 많아지면 그만큼 DML 속도가 느려지는 측명이 있지만 

   사용빈도를 감안해서 결정한 것이라면 잃는 것보다 얻는 것이 많다. 

버퍼 Pinning 효과 활용 

 - rowid에 따른 버퍼 Pinning 효과는 11g 부터 사용가능 

  (rowid에 따른 버퍼 Pinning 효과는 테이블 블록에 rowid 순으로 레코드가 들어있다는 것인가?)

수동으로 클러스터링 팩터 높이기 

 - 해당 인덱스 기준으로 테이블을 재생성함으로써 CF를 인위적 좋게 만드는 방법   

차세대 시스템 구축 시 주의사항 

 - 데이터 이관 시 ASIS 대비 TOBE 시스템의 CF가 나빠지지 않았는지 조사하고, 

   그 결과에 따라 적절한 조치를 취해주어야 한다. 

index clustering factor 

 - clustering_factor 수치가 테이블 블록에 가까울수록 데이터가 잘 정렬돼 있음을 의미하고, 

   레코드 개수에 가까울수록 흩어져 있음을 의미한다. 

클러스터링 팩터와 물리적 I/O 

 - 인덱스 CF가 좋다고 하면 인덱스 정렬 순서와 테이블 정렬 순서가 서로 비슷하다는 것을 의미

 - 이는 궁극적으로 물리적인 디스크 I/O 횟수를 감소시키는 효과를 가져다준다. 

 - clustering factor는 인덱스를 통해 테이블을 엑세스할 때 예상되는 논리적 I/O 개수를 

   더 정확히 표현하고 있다. 

버퍼 pinning에 의한 논리적 I/O 감소원리 

 - 인덱스를 통해 엑세스되는 하나의 테이블 버퍼 블록을 Pinning 한다. 

인덱스 손익분기점 

 - 인덱스가 Full table Scan 보다 더 느려지는 핵심적인 요인 

    ● index는 random 엑세스 , full table scan 은 sequential 엑세스 방식 

    ● index single block read , full table scan 은 Multiblock read

 - 인덱스 스캔 비효율이 없도록 구성된 인덱스를 이용해 부분범위처리르 방식으로 

   프로그램 구현시 인덱스의 효용성은 100%가 된다. 

손익분기점을 극복하기 위한 기능들 

 - IOT , 클러스터 테이블, 파티셔닝 

index range scan 

index full scan

 - 인덱스 스캔 후 필터가 많이 되어 random access 부하를 줄일 수 있을 때 

 - 필요한 컬럼이 인덱스에 모두 포함되어 있어서 따로 테이블 접근이 필요 없을 때

index Unique scan 

 - 등치 조건 검색에서 발생 

index skip scan 

 - 인덱스 선두 컬럼의 조건절이 빠졌어도 인덱스를 활용하는 방법  

   ( 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 개수가 많을 때 유리 )

 - 버퍼 pinning을 이용한 Skip scan 원리
    ● 래치 획득 과정 없이 버퍼 Pinning을 통해 블록을 곧바로 액세스할 때는 buffer is pinned count 항목의 수치가 증가한다.

    ● 버퍼 Pinning은 하나의 데이터베이스 Call(Parse Call, Execute Call, Fetch Call)내에서만 유효하다.

        (Call이 끝나고 사용자에게 결과를 반환하고 나면 Pin은 해제되어야 한다.
         따라서 첫 번째 Fetch Call에서 Pin된 블록은 두 번째 Fetch Call에서 다시 래치 획득 과정을 거쳐 Pin 되어야 한다.)

 - index skip scan 작동 조건 

    ● 선두 컬럼이 없을 때 

    ● 선두 컬럼은 있고 중간 칼럼이 빠졌을 때 

    ● 선두의 2개의 컬럼이 빠졌을 때 

    ● 선두 컬럼이 범위 검색 조건일 때   

  - In-list 와는 수행원리가 다름 

    (in-linst는 반복 수행) 

  - index range scan descending

index fast full scan 

 - 물리적인 순서로 블락을 읽어 들인다. (multi block I/O) 

   (index full scan 과 block I/o 양은 비슷하지만 수행 속도와 대기 이벤트량이 다르다.)

 - 테이블 엑세스 없이 index만 읽고 처리할 때 index range scan 이나 index full scan 시에도 

   multi block I/O가 발생 

 - index fast full scan의 특징 

    ● 세그먼트 전체를 스캔

    ● 결과집합 순서 보장 안 됨

    ● Multiblock I/O 

    ● 병렬스캔 가능 

    ● 인덱스에 포함된 컬럼으로만 조회할 때 사용가능 

 - index fast full scan의 호용 

    ● 스캔해야 할 건수는 많더라도 조건절에 필터링되고 나서 최종 결과 건수가 적을 때

        ( 부분범위 처리가 가능한 애플리케이션 환경이라면 역시 손해 볼 것 ) 

    ● 컬럼 개수가 많아 테이블보다 인덱스 크기가 현저히 작은 상황에서 효과 

    ● 병렬쿼리 가능 

and-equal, index combine, index join 

 - And-equal : 10g 부터 폐기된 기능 

 - Index Combine 

    ● 인덱스를 bit-map인덱스로 바꾸고 그것을 bit-wise 하는 것  

    ● 데이터 분포도가 좋지 않은 두개 이상의 인덱스를 결합해 

        테이블 Random 엑세스를 줄이는것이 목표

    ● And-equal 에 비해 향상된 점  

          조건절이 '=' 이어야 할 필요가 없고 

          Non unique 인덱스일 필요도 없다. 

          조건절이 or로 결합된 경우에도 유용

 - index join 

   ● 테이블 엑세스 없이 결과 집합을 만들기 위한 스캔 방식 

   ● index join은 해시 조인 매커니즘들 그대로 사용한다. 

   ● index join은 쿼리에 사용된 컬럼들이 인덱스에 모두 포함될 때만 작동 

        ( 둘 중 어느 한쪽에 포함되기만 하면 된다.)

* 인덱스를 이용한 소트 연산 대체   

 인덱스 사용 불가능 경우 

   - 인덱스 컬럼 조건걸 가공 

   - 부정형 비교 

   - is null 

     ( is null 결합 컬럼시 다른 조건절이 사용되면 인덱스 사용 가능한 경우도 있음) 

묵시적 형변환 

  - varchar2 컬럼에 숫자 값을 더하거나 빼는 연산을 가하면 내부적으로 숫자형으로 

    형변환이 일어난다. 

  - 숫자형과 문자형이 비교될 때는 숫자형이 우선시 된다. 

    (만약 문자형 컬럼에 숫자로 변환할 수 없는 문자열이 들어 있으면 쿼리 에러) 

  - like로 비교 할 때는 숫자형이 문자형으로 변환 

  - decode(a,b,c,d) decode 함수의 형은 c 인자에 따라 결정

    ( null 일시 varchar2)  

함수 기반 인덱스 

  - 급할시 임시 방편으로 사용함