성능고도화 6-3. 인덱스 파티셔닝

인덱스 파티션 유형 

 - 인덱스 파티션 종류 

    ● 비파티션 인덱스 

    ● 글로벌 파티션 인덱스 

    ● 로컬 파티션 인덱스 

 - 참고로, 비파티션 테이블에 대한 비트맵 인덱스는 파티셔닝이 허용되지 않고, 파티션 테이블에

   대한 비트맵 인덱스는 로컬 파티셔닝만 허용된다. 

로컬 파티션 인덱스 

 - 로컬 파티션 인덱스는 항상 테이블 파티션과 1:1 관계를 형성하므로 만약 테이블이 

   결합 파티셔닝 돼 있다면 인덱스도 같은 단위로 파티셔닝 된다.  

 - 로컬 파티션 인덱스가 갖는 장점은 무엇보다 관리적 편의성에 있다. 테이블 파티션 구성에 

   변경이 생기더라도 인덱스를 재생성할 필요가 없어 관리 비용이 아주 적다 .

비파티션 인덱스 

 - 비파티션 인덱스는 말 그래도 파티셔닝하지 않은 인덱스를 말한다. 테이블이 파티셔닝 돼 

   있다면 1:M 관계에 놓인다. 

글로벌 파티션 인덱스 

 - 글로벌 파티션 인덱스는 테이블 파티션과 독립적인 구성을 갖도록 파티셔닝하는 것을 말한다. 

   테이블은 파티셔닝돼 있지 않을 수도 있다. 

 - 독립적 구성이라는 점에서 효용성이 높을 것처럼 보이지만 몇몇 제약사항 때문에 오히려 

   효용성이 낮은 편이다. 

 - 글로벌 파티션 인덱스 사용을 불편하게 만드는 가장 큰 제약은, 기준 테이블의 파티션 구성에 

   변경이 생길 때마다 인덱스가 unusable 상태로 바뀌고 그때마다 인덱스를 재생성해야 한다는 

   것이다. 이 제약은 비파티션 인덱스 일 때도 똑같이 나타난다. 

 - 9i부터 아래와 같이 update global indexes 옵션을 주면 파티션 DDL 작업에 의해 영향 받는 

   인덱스 레코드를 자동으로 갱신해 주므로 인덱스가 unusable 상태로 빠지지 않는다. 

 - 하지만, 파티션 DDL로 인해 영향 받는 레코드가 전체의 5% 미만일 때만 유용하다. 

   다시 말해, 5% 이상일 때는 인덱스를 재생성하는 것보다 오히려 늦다는 뜻이다. 

   ( 항상 들어맞는 수치는 아니며, 평균적으로 그렇다는 것이다. 

     손익분기점과 같은 개념으로 이해하면 된다. ) 

테이블 파티션과의 관계 

 - 오라클이 자동으로 관리해 주는 1:1 관계가 아닌 파티션 인덱스는 

   모두 글로벌 파티션 인덱스라고 설명했다. 

 - 참고로, 로컬 파티션 인덱스처럼 테이블과 1:1 관계가 되도록 수동으로 구성하더라도 

   여느 글로벌 파티션과 마찬가지로 기준 테이블 구성에 변경이 발생할 때마다 

   인덱스를 재생성해야 한다.  

글로벌 해시 파티션 인덱스 

 - 글로벌 파티션 인덱스의 경우, 9i까지는 글로벌 Range 파티션만 가능했지만 10g부터는 글로벌 

   해시 파티션도 가능해졌다. 즉, 테이블과 독립적으로 인덱스만 해시 키 값에 따라 

   파티셔닝할 수 있게 되었다.     

- 글로벌 해시 파티션 인덱스는 Right Growing 인덱스 처럼 Hot 블록이 발생하는 인덱스의 

  경합을 분산할 목적으로 주로 사용된다. 

- 글로벌 결합 인덱스 파티셔닝은 여전히 불가능하다. 

Prefixed vs Nonprefixed 

 - prefixed 와 Nonprefixed의 구분은 인덱스 파티션 키 컬럼이 인덱스 구성상 왼쪽 선두 컬럼에 

   위치하는지에 따른 구분이다. 

    ● Prefixed : 파티션 인덱스를 생성할 때, 파티션 키 컬럼을 인덱스 키 컬럼 왼쪽 선두에 

                     두는 것을 말한다. 

    ● Nonprefixed : 파티션 인덱스를 생성할 때, 파티션 키 컬럼을 인덱스 컬럼 왼쪽 선두에 

                           두지 않는 것을 말한다. 파티션 키가 인덱스 커럶에 아예 속하지 않을 때도 

                           여기에 속한다. 

 - 로컬과 글로벌, Prefixed 와 Nonprefixed 조합 

    ● 비파티션 인덱스 

    ● 글로벌 Prefixed 파티션 인덱스 

    ● 로컬 Prefixed 파티션 인덱스 

    ● 로컬 Nonprefixed 파티션 인덱스 

파티션 인덱스 구성 예시 

 - 로컬 파티션 인덱스에서 Unique 파티션 인덱스를 만들 때는 파티션 키 컬럼이 인덱스 컬럼에 

   포함돼 있어야 한다. ( 그렇지 않을 경우 ORA-14039 error 발생 ) 

 - 비파티션 인덱스를 만들 때는 그런 제약이 없으므로 위와 같이 에러 없이 Unique 인덱스가 

   만들어진다. 

 - 로컬 파티션 인덱스에는 Nonprefixed가 허용되지만 

   글로벌 파티션 인덱스에는 허용되지 않는다.  

 - 참고로, 비파티션 인덱스에는 이런 제약이 없다. 

 - user_indexes 와 user_part_indexes  테이블을 통해 파티션 인덱스의 종류를 확인할 수 있다. 

글로벌 파티션 인덱스의 효용성 

 - 결론은 글로벌 파티션 인덱스는 경합을 분산시키려고 글로벌 해시 파티셔닝 하는 경우외에는 

   거의 사용되지 않는 실정 

 - 파티션 테이블에 대한 글로벌 파티션 인덱스의 효용성 

   ● 테이블과 같은 컬럼으로 파티셔닝하는 경우 

      - 이 부분을 이해 하는데 좀 생각이 필요했는데 여기서 예시는 테이블 파티션 값은 월별 

        인덱스 파티션은 분기별로 나누었다. 같은 컬럼을 사용했다고 하니 범위를 다른게 했던지 

        했을 것이다. 그런데 여기서는 날짜는 between으로 자주 사용하기 때문에 글로벌 인덱스가 

        파티션 테이블 키 값보다 넓은 값을 가지고 있으면 상대적으로 많은 값이 있는 파티션들을 

        읽어야 하기 때문에 비효율이 있을 것이라고 말하고 있다. 

      - 그래서 결론은 NL 조인에서 넓은 범위 조건을 가지고 Inner 테이블 엑세를 위해 자주 

        사용된다면 비파티션 인덱스가 가장 좋은 선택이라고 한다. 

      - 그런데 이부분은 사실상 왜 이렇게 글로벌 파티션 인덱스를 구성했는지가 궁금하다 

        이유는 처음 부터 비효율적으로 생성하기 때문이라서 실제로 현업에서 

        이런 케이스가 있는지 설명을 위해서 만들어 낸것인지 궁금하다. 

   ● 테이블과 다른 컬럼으로 파티셔닝 하는 경우 

      - 테이블 파티션 기준인 날짜 이외 컬럼으로 인덱스를 글로벌 파티셔닝 할 수 있는데, 

        그런 구성은 대게 인덱스를 적정 크기로 유지하려는 데에 목적이 있다. 

        인덱스가 너무 커지면 관리하기 힘들고 인덱스 높이가 증가해 엑세스 효율도 나빠지기 

         때문이다. 

      - 하지만 그런 장점도 로컬 파티션 인덱스 때문에 무색해진다. 

        글로벌 파티션이 비파티션 보다 관리상 이점이 있다고는 하나 로컬 파티션만 못하고, 

        인덱스 높이 조절 측면에서도 그렇다. 

로컬 Nonprefixed 파티션 인덱스의 효용성 

 - 결국은 쿼리에서 where 절에 다른 컬럼이 등치 조건일 때 유리할 수 있다. 

글로벌 Prefixed 파티션 인덱스와 비교 

 - 글로벌 파티션 인덱스는 prefixed 파티션만 허용되므로 로컬 prefixed 파티션과 마찬가지로 

   인덱스 스캔 효율이 나쁘다. 

 - 더욱이 과거 파티션을 제거하고 신규 파티션을 추가하는 등의 파티션 단위 작업 시 

   매번 인덱스를 재생성해야 하므로 관리적 부담이 크다. 

비파티션 인덱스와 비교 

 - 비파티션 인덱스를 이용하더라도 관리적 부담은 글로벌 파티션과 동일하게 발생한다. 

   관리적 비용 측면에서는 로컬 Nonprefixed 파티션 인덱스가 훨씬 낫다. 

 - 조회 측면에서 로컬 Nonprefixed 파티션 인덱스는, 파티션 키 값 2개 이상의 넓은 범위의 

   조건으로 조회할 때 여러 인덱스를 (수직적) 탐색해야 하는 비효율이 있다. 

 - 반면 등치 조건을 선두에 둔 비파티션 인덱스는 파티션 키 값이 여러개 걸친 조건으로 

   조회하더라도 인덱스 스캔 상 비효율은 없다. 

 - 하지만 아주 넓은 범위의 파티션 키 컬럼을 조회하거나 등치 조건만으로 조회할 때는 

   테이블 Random 엑세스 부하 때무에 비파티션 인덱스도 제 성능을 내기 어렵다. 

   이럴 때는 병렬 쿼리가 필요할 수 있는데, 아쉽게도 비파티션 인덱스에는 병렬 쿼리가 

   허용되지 않는다. 

 - 로컬 Nonprefixed 파티션 인덱스라면 여러 병렬 프로세스가 각각 하나의 인덱스 세그먼트를 

   스캔하도록 함으로써 위 쿼리의 응답 속도를 크게 향상시킬 수 있다. 

엑세스 효율을 고려한 인덱스 파티셔닝 선택 기준 

 - DW성 애플리케이션 과 OLTP성 애플리케이션 환경 비교 

    ● DW성 애플리케이션 환경 

         - DW/DSS 애플리케이션에는 날짜 컬럼 기준으로 파티셔닝된 이력성 대용량 테이블이 

           많다. 따라서 관리적 측면뿐만 아니라 병렬 쿼리 활용 측면에서도 로컬 파티션 인덱스가 

           좋은 선택이다. 

           ( 비파티션 인덱스는 index fast full scan이 아닌 한 병렬 쿼리에 활용할 수 없다. ) 

         - 로컬 인덱스 중에서는 Nonprefixed 파티션 인덱스가 성능 면에서 유리할 때가 많다.  

    ● OLTP성 애플리케이션 환경 

         - OLTP성 애플리케이션 환경에서는 비파티션 인덱스가 대게 좋은 선택이다.  

         - Right Growing 인덱스에 대한 동시 Insert 경합을 분산할 목적으로 해시 파티셔닝하는 

           경우가 아니라면 글로벌 파티션 인덱스 효용성이 낮다. 

         - OLTP성이더라도 테이블이 파티셔닝돼 있다면 인덱스 파티셔닝을 고려할 수 있는데, 

           특히 로컬 파티션 인덱스는 테이블 파티션에 대한 DDL 작업 후 인덱스를 재생성하지 

           않아도 되므로 가용성 측면에서 유리하다. 

         - 파티션 키에 대한 조건절이 없을 때 로컬 Prefixed 인덱스는 아예 사용이 안 되는 반면 

           Nonprefixed 인덱스는 비효율이 있을지언정 잘 사용된다. 

         - 정리하면, OLTP 환경에서 로컬 인덱스를 선택했다면 Prefixed 파티션이든 

           Nonprefixed 파티션이든 검색 조건에 항상 사용되는 컬럼을 파티션 키로 선정하려고 

           노력해야 한다. 파티션 키가 범위검색 조건으로 자주 사용된다면 Nonprefixed 인덱스가 

           유리하고, 될 수 있으면 좁은 범위검색이어야 한다. 특히, NL 조인에서 파티션 키에 대한 

           넓은 범위검색 조건을 가지고 Inner 테이블 엑세스 용도로 사용된다면 비파티션 인덱스를

           사용해야 한다.

인덱스 파티셔닝 제약을 고려한 데이터베이스 설계 

 - 인덱스 파티셔닝에 대한 반드시 기억해야 하는 2가지 제약 

    ● Unique 파티션 인덱스를 정의할 때는 인덱스 파티션 키가 모두 인덱스 구성 컬럼에 

        포함돼야 한다. 이 제약이 없다면 인덱스 키 값을 변경하거나 새로운 값을 입력할 때마다

        중복 값 체크를 위해 매번 많은 인덱스 파티션을 탐색해야 하므로 DML 성능이 저하된다. 

        따라서 당연히 필요한 제약이라고 할 수 있다. 

    ● 글로벌 파티션 인덱스는 Prefixed 파티션이어야 한다. 

- 파티셔닝은 인덱스 전략 수립과 병행해야 한다. 자주 사용되는 엑세스 패턴과 데이터 분포를 

  고려해 인덱스 전략을 수립하고, 인덱스만으로 빠른 성능을 내기 어려운 엑세스 경로를 파악해 

  테이블 파티셔닝과 인덱스 파티셔닝 전략을 수립해야 한다.