[Oracle] 로컬 인덱스와 글로벌 인덱스에 대하여 (파티션 테이블 인덱스)

파티션 테이블에는 일반적인 테이블에서 사용하는 인덱스로는 사용이 힘듭니다. 파티션 테이블의 특성상 새로운 세그먼트를 계속해서 생성하여 테이블을 계속 나뉘어야 하지만 인덱스는 나눌 수가 없기 때문입니다. 그렇다면 파티션 테이블에는 인덱스를 어떻게 걸어야 할까요? 파티션 테이블에는 다른 방식으로 인덱스를 걸어주어야 합니다. 오라클에는 파티션 테이블에 인덱스를 거는 두 가지 방식이 있습니다. 바로 로컬 인덱스와 글로벌 인덱스입니다.

 

 로컬 인덱스와 글로벌 인덱스 

 

• 한 파티션에서 조회하는 액세스 패스는 Local 인덱스를 사용하도록 함
• 운용측면에서 Global Index보다는 Local Index 사용이 권장
• Table Partition Key를 Index로 설정할 경우  Local Prefixed Index를 사용
• PK 컬럼은 Table Partition Key를 첫 번째 컬럼으로 하는 Local Prefixed Index를 사용
• Non-partition 컬럼에 대한 빈번한 배치 작업 수행 시 Local Nonprefixed Index를 사용
• Non-partition 컬럼에 대한 Unique Index 설정 시 Global Index를 사용 (OLTP)

 

 로컬 인덱스 

 

 

파티션 테이블에서 사용하는 가장 일반적인 INDEX 생성 방법으로 테이블과 인덱스가 동일하게 파티션 된 경우를 말합니다. 로컬 인덱스는 테이블 파티션 키 컬럼을 똑같이 인덱스로 구성합니다. 예를 들어 파티션이 10개라면 인덱스도 똑같이 10개가 됩니다. 로컬 파티션 인덱스의 경우에는 테이블의 파티션 구조가 바뀐다거나 파티션이 삭제가 된다고 하더라도 인덱스 재생성이 필요가 없이 오라클에서 알아서 자동으로 관리를 해주기 때문에 관리측면에서 유리한 측면이 있습니다.

 

로컬 인덱스 생성 방법

-- 파티션 테이블 생성
create table T_ORDER (
  order_no       number,
  order_dt       VARCHAR2(8),
  product_nm     VARCHAR2(30),
  price          number
)
partition by range (order_dt)
(
  partition ORDER_2206 values less than (20220699),
  partition ORDER_2205 values less than (20220599),
  partition ORDER_2204 values less than (20220499)
);

-- 글로벌 파티션 인덱스 생성
create index IDX_SALES_01 on T_ORDER (order_no) LOCAL;

 

 글로벌 인덱스 

글로벌 인덱스는 파티션 테이블로 이루어져 있는 테이블에 하나의 인덱스가 여러 개의 테이블 파티션과 매핑됩니다. 글로벌 인덱스는 파티션 키 컬럼을 인덱스로 지정해주어야 하는 로컬 인덱스와는 달리 다른 컬럼들도 자유롭게 인덱스를 걸 수 있다는 장점이 있습니다.

 

글로벌 인덱스로 생성하면 좋을 경우

• 테이블 파티션 키와 인덱스 파티션 키가 서로 다른 경우
• 테이블과 같은 컬럼으로 파티션 되지만 범위가 서로 다르거나 다른 컬럼으로 파티션이 이루어지는 경우

 

글로벌 인덱스 생성 방법

-- 파티션 테이블 생성
create table T_ORDER (
  order_no       number,
  order_dt       VARCHAR2(8),
  product_nm     VARCHAR2(30),
  price          number
)
partition by range (order_dt)
(
  partition ORDER_2206 values less than (20220699),
  partition ORDER_2205 values less than (20220599),
  partition ORDER_2204 values less than (20220499)
);

-- 글로벌 파티션 인덱스 생성
create index IDX_ORDER_01 on T_ORDER (product_nm) GLOBAL
partition by hash (product_nm)
partitions 4;

 

 로컬 인덱스를 많이 사용하는 이유 

파티션 테이블의 인덱스를 구성할 수 있는 두 가지 방법인 로컬 인덱스, 글로벌 인덱스 위 두 가지 방식 중 로컬 파티션 인덱스를 압도적으로 많이 사용합니다. 그렇다면 그 이유는 무엇일까요? 로컬 인덱스가 관리가 편하기 때문입니다. 예를 들어 로그를 저장하는 테이블을 파티션 테이블로 만들었다고 해봅시다. 그리고 시간이 지나 로그의 보관 의무 시간이 종료되어 필요 없는 데이터들을 제거한다고 해봅시다. 이때 파티션 DROP을 수행하게 될 텐데 글로벌 인덱스로 구성되어 있다면 필요 없는 테이블 파티션을 DROP 한 뒤에 인덱스가 깨져버립니다.

 

ORA-01502: index or partition of such index is in usable state tips
ORA-01502: 인덱스 또는 인덱스의 분할 영역은 사용할 수 없는 상태입니다.

↑ 위와 같은 에러가 발생하게 됩니다.

 

이렇게 인덱스가 깨져버리면 Invalid 상태가 되고 이 인덱스를 다시 사용하기 위해서는 인덱스 리빌딩 작업을 다시 해줘야 등 번거로움이 있습니다. 하지만 로컬 인덱스로 구축하였다면 전혀 문제가 발생하지 않습니다. 로컬 인덱스는 각각의 파티션마다 하나씩 걸려 있기 때문에 파티션을 DROP 할 때 함께 제거되고 남아있는 파티션에는 전혀 지장이 없기 때문입니다.