꿈꾸는 개발자, DBA 커뮤니티 구루비

파티션 인덱스 종류

파티션 인덱스 분류 기준

Global / Local

① Global : 테이블 파티션 키와 인덱스 파티션 키가 서로 다름
- Global Index는 테이블과 다르게 파티션 되는 경우 입니다.
- 즉 테이블과 같은 칼럼으로 파티션 되지만, 그 범위가 틀리거나 혹은 다른 칼럼으로 파티션이 이루어지는 경우 입니다.

② Local : 테이블 파티션 키와 인덱스 파티션 키가 같음
- 로컬 인덱스란 인덱스를 생성한 테이블과 파티션 된 인덱스가 동일하게 파티션 된 (Equi-Partition) 경우를 나타냅니다.
- 즉, 인덱스와 테이블은 같은 칼럼에 의해 파티션 되며, 하나의 인덱스 파티션이 테이블 파티션 하나와 대응되며, 대응되는 인덱스 파티션과 테이블 파티션은 각각 같은 범위를 갖게 됩니다.

※ Partitioned Table에서는 대부분의 경우 Local Index가 성능이나, 가용성 및 관리의 용이성이 Global Index 보다 우수 합니다. 그러나 업무의 특성 및 기타 Access 의 형태에 따라 Partition Index를 만들고자 하나 Local Index를 생성할 수 없는 경우에 Global Index를 고려 합니다.

Prefixed / Non-prefixed

① Prefixed : 인덱스 첫 번째 컬럼이 인덱스 파티션 키와 같음

② Non-prefixed : 인덱스 첫 번째 컬럼이 인덱스 파티션 키와 다름

※ Partitioned Index 생성시 Prefixed/Non-prefixed에 대한 지정은 하지 않으며 위와 같은 Rule로 만들어 졌는지 여부를 DBMS가 확인하여 Dictionary(USER_PART_INDEXES.ALIGNMENT)에 기록하고 관리하게 됩니다.

앞으로 진행될 Local Prefixed Index, Local non-prefixed Index, Global prefixed Index의 예제는 위에서 설명한 Range Partition 테이블의 예제로 설명을 하겠습니다.

 
-- Range Partition 강좌 때 생성한 sales 테이블
SQL> CREATE TABLE sales
       (sales_no NUMBER,
        sale_year INT NOT NULL,
        sale_month INT NOT NULL,
        sale_day INT NOT NULL,
        customer_name  VARCHAR2(30),
        price NUMBER)
       PARTITION BY RANGE (sale_year, sale_month, sale_day)
       (PARTITION sales_q1 VALUES LESS THAN (2005, 01, 01) TABLESPACE ASSM_TBS1,
        PARTITION sales_q2 VALUES LESS THAN (2005, 07, 01) TABLESPACE ASSM_TBS2,
        PARTITION sales_q3 VALUES LESS THAN (2006, 01, 01) TABLESPACE ASSM_TBS3,
        PARTITION sales_q4 VALUES LESS THAN (2006, 07, 01) TABLESPACE ASSM_TBS4 );

Local Prefixed Index

- 위에서 설명한 거와 같이 Local의 의미는 테이블 파티션 키와 인덱스 파티션 키가 같음을 의미 합니다.

- Prefixed 는 인덱스 첫 번째 컬럼이 인덱스 파티션 키와 같음을 의미 합니다.

- Range Partition 테이블의 Local Prefixed Index 예제 입니다.

 
-- Range Partition으로 생성된 sales 테이블에 Local Prefixed Index를 생성한 예제
SQL> CREATE INDEX sales_idx01
     ON sales(sale_year, sale_month, sale_day) LOCAL
       (PARTITION idx_sales_p1 TABLESPACE IDX1,
        PARTITION idx_sales_p2 TABLESPACE IDX2,
        PARTITION idx_sales_p3 TABLESPACE IDX3,
        PARTITION idx_sales_p4 TABLESPACE IDX4 );

- Hash Partition 테이블의 Local Prefixed Index 예제 입니다.

 
-- Hash partition 테이블에서는 STORE IN을 사용하여 인덱스 테이블스페이스를 지정.
-- 위에 예제로 생성된 sales_hash 테이블에 Local Prefixed Index를 생성한 예제
SQL> CREATE INDEX sales_hash_idx01 ON sales_hash(sales_no)
     LOCAL STORE IN (IDX1, IDX2, IDX3, IDX4);

Local non-prefixed Index

- Index의 첫 번째 컬럼이 파티션 키 컬럼으로 시작하지 않는 Local Index 입니다.

- 파티션 키 컬럼이 Index에 중간에 올 수도 있지만 항상 선두에 오지 않는 한 Non-prefixed index 입니다

- 아래는 Range Partition 테이블의 Local non-Prefixed Index 예제 입니다

 
SQL> CREATE INDEX sales_idx02
     ON sales(sales_no, sale_year) LOCAL
       (PARTITION idx_sales_p1 TABLESPACE IDX1,
        PARTITION idx_sales_p2 TABLESPACE IDX2,
        PARTITION idx_sales_p3 TABLESPACE IDX3,
        PARTITION idx_sales_p4 TABLESPACE IDX4 ) ;

Global prefixed Index

- Global Index는 테이블과 다르게 파티션 되는 경우 입니다. 즉 테이블과 같은 컬럼으로 파티션 되지만, 그 범위가 틀리거나 혹은 다른 컬럼으로 파티션이 이루어지는 경우 입니다.

- Global Index 생성시 인덱스 칼럼의 맨 처음 칼럼을 사용하여 파티션 되면, 생성된 인덱스는 Global Prefixed Index 입니다.

- 다른 칼럼을 사용하여 파티션 되는 경우에는 Global Non-Prefixed Index이지만, 오라클 에서는 Global Prefixed Index만을 지원 합니다.

- Global Index는 항상 인덱스의 맨 앞 칼럼 값만을 이용하여 파티션 됩니다.

- 아래는 Range Partition으로 생성된 sales 테이블 sale_year 컬럼에 Global prefixed Index로 생성 예제 입니다.

 
-- Range Partition으로 생성된 sales 테이블 sale_year 컬럼에 Global prefixed Index로 생성 예제
SQL> CREATE INDEX sales_idx03 
     ON sales(sale_year)
     GLOBAL PARTITION BY RANGE (sale_year)
       (PARTITION idx_sales_year_p1 VALUES LESS THAN (2005) TABLESPACE IDX1,
        PARTITION idx_sales_year_p2 VALUES LESS THAN (2010) TABLESPACE IDX2,
        PARTITION idx_sales_year_p3 VALUES LESS THAN (MAXVALUE) TABLESPACE IDX3);

아래는 Range Partition으로 생성된 sales 테이블 sale_month 컬럼에 Global prefixed Index로 생성하는 예제 입니다.

 
-- Range Partition으로 생성된 sales 테이블 sale_month 컬럼에 Global prefixed Index로 생성 예제
SQL> CREATE INDEX sales_idx04 
     ON sales(sale_month)
     GLOBAL PARTITION BY RANGE (sale_month)
       (PARTITION idx_sales_month_p1 VALUES LESS THAN (04) TABLESPACE IDX1,
        PARTITION idx_sales_month_p2 VALUES LESS THAN (07) TABLESPACE IDX2,
        PARTITION idx_sales_month_p3 VALUES LESS THAN (10) TABLESPACE IDX3,
        PARTITION idx_sales_month_p4 VALUES LESS THAN (MAXVALUE) TABLESPACE IDX4);

파티션 강좌 참고문헌

- Oracle® Database Administrator’s Guide 10g Release 1 (10.1)
- 문서 "Partition Table 과 Index 관리" 김주연 한국오라클 (주) 제품지원실
- 문서 "인덱스 파티션 설계방법" Reviewed by Oracle Certified Master Korea Community
- 엔코아 솔루션 웨어하우스 "Partition Table 의 Index" (www.en-core.com)
- Oracle Technical Bulletins No. 11269 PARTITION TABLE 이란?
- Oracle Technical Bulletins No. 22103 (V9.2) RANGE LIST PARTITIONING - ORACLE 9.2 ENHANCEMENT