파티션 인덱스
파티션 인덱스 종류
파티션 인덱스 분류 기준
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