본 연재는 지난 "파티션의 개념 및 소개", "파티션키(Partition Key)의 선정 "에 이어 "Partitioned Table의 Index"에 대해서 각 Type별 개념과 활용방안에 대하여 알아보고자 한다. Oracle 8.X 데이터베이스를 기준으로 언급하며 이 외의 경우에는 대상을 명시하기로 한다.

1. Partition Index 분류 기준

1.1. Global / Local

Local index는 Table에 생성한 Index가 Table의 Partition과 Equipartitioning(동등분할)된 경우이다. Equipartitioning되었다는 것는 Table과 Index간에 동일한 분할 방식과 동일한 Partition Key에 의해 분할 되었고, Partition 수가 같으며, 동일한 Partitioning Bound를 갖게 됨을 의미한다. Local index를 생성시 Partition key를 지정하지 않아도 되는 것은 Equipartitioning이 동일한 Partition key를 가져야 하므로 모 테이블의 Partition key가 그대로 Index에 적용되기 때문이다.

Global Index는 굳이 Table Partition과 Index Partition이 동일할 필요가 없으며 또한 Index Partitioning이 Global Index의 필요 조건은 아니다.

1.2. Prefixed / Non-prefixed

Index가 단일 Column으로 구성되었든 결합 Index로 구성되었든 인덱스의 가장 왼쪽에 위치한 Column이 Index의 Partition Key로 이루어져 있으면 Prefixed Index가 된다.

예를 들어 Index Partition Key가 (Col_A + Col_B)인 Index의 구성컬럼이 (Col_A + Col_B + Col_C)로 결합되어져 있다면 Prefixed index가 된다.

Partitioned Index 생성시 Prefixed/Nonprefixed에 대한 지정은 하지 않으며 위와 같은 Rule로 만들어 졌는지 여부를 DBMS가 확인하여 Dictionary(user_part_indexes.alignment)에 기록하고 관리하게 된다.

개념상 주의할 것은 Prefixed index와 Non-perfixed Index의 구분은 Index의 Partition Key와 Index Column간의 관계이지 Table의 Partition Key와 Index Column간의 관계를 나타내는 것은 아니다.

Local Index의 경우 위에서 언급했듯이 테이블과 Equipartitioning 되므로 Index Partition key와 Table Partition Key와 동일하다. 그러므로 Local Index를 Prefixed로 만들면 Table의 Partition Key가 Local Index의 선두에 위치하게 된다.

Local Index의 이러한 특성으로 인해 Prefixed Index의 Prefix는 Table Partition Key와 같다고 잘못 생각하기도 한다.

Global Partitioned Index의 관점에서 보면 모 Table의 Partition key와 동일한 Partition key를 가져야 할 이유가 없으므로 Global Index의 Prefix는 모 Table의 Partition Key와 다르게 나타나게 된다.

2. Partition Index 유형

Partition table의 Index는 아래 4가지의 Type으로 나눌수 있다.

Local prefixed partitoned index

Local non-prefixed partitoned index

Global prefixed non-partitoned index

Global prefixed partitoned index

Oracle 데이터베이스에서는 Global nonprefixed유형은 Global prefixed유형에 비해 추가적인 장점은 없는 반면 Partition했을 경우 Partition분리에 따른 Overhead만 존재하기 때문에 지원하지 않고 있으며 위의 Type에도 포함하지 않는다

각각의 Type에 대하여 세부사항과 활용방법, 사용예를 아래 그림을 참고하며 살펴보자.

[ 그림 1 ]

2.1. Local Prefixed Index

Index Column의 구성에서 선두 Column이 Partition Key Column인 Index이다.

이 Partition Key는 Local Index가 모 Table과 Equipartitioning되어 있으므로Table의 Partition Key와 동일하다. 즉 각 Table Partition에 Row가 Partition Key 값에 따라 분산되어 있고, Index는 각 Partition에 분리된 값의 기준(Partitioning Key)에 따라 정렬되어 Index가 구성된다.

Unique Local Partitioned Prefixed Index 생성 Script

CREATE UNIQUE INDEX RANGE2_LPUK1 ON 
TS_SALES_1997_RANGE2(SALE_month,sale_day,serial ) 
TABLESPACE  PKIS_DATA2 PCTFREE  10 
STORAGE(INITIAL 100K NEXT 100K PCTINCREASE 0  ) LOCAL;

위의 Index 생성 Script에서 PREFIXED를 지정하지 않았지만 Dictionary정보를 검색해 보면 DBMS가 Index의 선두 Column을 조사하여 PREFIXED로 지정함을 알 수 있다. 또한 위의 Index 생성 Script에서는 Index가 몇 개의 Partition으로 나누어 지는지 지정하지 않았다.

그러나 이부분 역시 "Local" Option으로 인해서 DBMS가 동일한 수의 Partition으로 나누었고 각 Index의 Partitioning Bound역시 Table의 Partitioning Bound와 동일하게 설정하였음을 Dictionary를 조사해 보면 알 수 있다. 즉 스스로 Equipartitioning 된 것이다.

USER_PART_INDEXES, USER_IND_PARTITIONS, USER_TAB_PARTITIONS를 조회하면 위의 내용을 확인할 수 있다.

2.2. Local Non-prefixed Index

Index의 선두Column이 Partition Key Column으로 시작하지 않는 Local Index이다.

Partition Key Column이 Index에 중간에 올 수도 있지만 항상 선두에 오지 않는 한 Nonprefixed index이다. 즉 Local Prefixed Index와 다른 점은 두 Index Type 모두 Partition Key에 따라 동일한 범위에 값을 가지고 있지만 Local Prefixed Index는 Partition Key를 기준으로 정렬되어 있는 반면, Nonprefixed Index는 정렬기준이 Partition Key가 아니라는 점이다.

Local Nonprefixed Index는 Unique Index가 되려면 Index column에 Partition Key Column을 포함하고 있어야 하는 제약을 안고 있다.

이러한 제약으로 인해 Partition Key가 포함되지 않은 Column들로 Unique Index를 만들고자 할 경우는 Global Index로 만들어야 한다.

Local Nonprefixed Index는 Table과 Equipartitioning 되어 있어 가용성, 관리의 용이성을 제공하면서도 Partition Key 이외의 Column에 의한 조건 검색시 Partition별 처리를 할 수 있으므로 Historical Table에서 유용하게 사용되어 질 수 있다.

Local Nonprefixed Index는 Index의 선두 Column이 Table Partition Key와 다를 뿐이며 생성하는 것은 Local Prefixed Index와 동일하다

2.3. Global Prefixed Partitioned Index

Global index는 local index에 비하여 생성시, 전체적인 활용 측면 및 영향을 충분히 고려하여 설계하고 생성해야 한다. 모 Table의 특정 Partition이 Drop된 경우 Global Index의 특정 Partition에만 영향이 있다고 간주 할 수 없기 때문에 모든 Global Index에 영향을 미친다.

Partition table의 경우 일정기간동안 데이타를 On-line으로 유지하고 그 기간이 지난 데이타는 백업받은 후 해당 Partition을 Drop하게 되는데 Global Index는 Partitioned Table에 대해서 Partition별 독립성이 없기 때문에 이 경우에는 전체 Index에 대해 수정 사항이 반영되도록 Rebuild작업을 해주어야 한다.

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

가령 Partition Key를 포함하지 않으면서 몇 개의 Column이 결합된 Unique Index를 만들어야 할 때, 또는 Index 선두Column을 구성하는 Column이 업무적으로 또는 Access 형태별로 분리가 확실하여 Partition 되는 Index가 너무 많지 않게 생성할 수 있을 경우에 활용 할 수 있다.

Global Prefixed Partitioned Indexes는 Global 인덱스가 Partitioned 되어 있는 경우이며 관리측면을 고려하여 관리가 용이한 레벨에서 파티션을 고려하면 된다. 그리고 파티션 단위는 테이블의 파티션 조각들과 1:1 맵핑될 필요 없이 자유롭게 정하되 너무 많은 파티션으로 구성하면 넓은 범위 Index Range Scan시 인덱스 Jumping 현상이 생길 수 있으므로 주의하여야한다.

만일 Global인덱스를 Local Indexes와 같이 파티션 조각들과 일치하는 Equi-Global Partitioned Indexed를 생성한 경우는 Oracle은 이를 이용하여 실행계획을 세우거나, DDL문이 실행된 경우 Local Index와 같은 Partitioning의 이점을 활용하지 못하므로 유의하여야 한다.

CREATE INDEX global_partitioned ON t_part (prod_id) 
   GLOBAL
   PARTITION BY RANGE (prod_id)
   (PARTITION pt01 VALUES LESS THAN (02000) TABLESPACE pts1,
     PARTITION pt02 VALUES LESS THAN (04000) TABLESPACE pts2,
     PARTITION pt03 VALUES LESS THAN (06000) TABLESPACE pts3,
     PARTITION pt31 VALUES LESS THAN (MAXVALUE)
   );

2.4. Global Prefixed Non-Partitioned Index

인덱스가 Partitioned 되어 있지 않은 형태로 일반적인 형태이다. Primary Key를 생성하게 되면 기본적으로 이 형태로 만들어진다.

CREATE INDEX global_partitioned ON t_part (prod_id) GLOBAL;

3. Local Index로 PK 구현 방안

Partition table에 index를 생성할 때 Default로 생성되는 index type은 Golbal index이기 때문에 Pk생성시 생성되는 Pk unique Index도 Global index로 만들어진다.

또한 Pk지정시 Local Option을 사용할 수 없으므로 Pk index를 Local로 만들기 위해서는 약간의 트릭을 필요로 한다.

CREATE TABLE test_t
PARTITION BY RANGE (col1)
(
  PARTITION test_part1 VALUES LESS THAN ('11') ,
  PARTITION test_part2 VALUES LESS THAN ('21') ,
  PARTITION test_part3 VALUES LESS THAN ('31') 
) as
select '01' col1, 'A' col2, 1 col3 from dual union all
select '11' col1, 'B' col2, 2 col3 from dual union all
select '21' col1, 'C' col2, 3 col3 from dual;
 
CREATE UNIQUE INDEX test_t_pk ON test_t (col2,col1) LOCAL ;

alter table test_t
add constraint test_t_pk primary key(col2,col1);

위 Script에서와 같이 Local unique index를 먼저 만든 후 이를 이용하여 Pk Constraint를 생성하면 Pk index는 이미 만들어져 있는 Unique index를 이용하게 된다.

유의할 점은 Local unique index명과 Pk Constraint명을 동일하게 주어야 하며 Local index가 Unique하기 위해서는 반드시 Partition key를 포함하여야 하듯이 이 경우도 Pk컬럼에 Partition key가 포함될 경우에 사용할 수 있다.

이렇게 함으로서 임의의 Partition이 Drop되더라도 Pk index와 Constraint를 유지할 수 있어 관리적인 측면에서 Local index의 장점을 살릴 수 있다.

이상으로 Partiton table의 Index에 대하여 알아보았으며 다음 연재에서는 Partition table의 효율적인 관리방안에 대하여 다루게 될 것이다.