[Oracle] Partitioned index

오라클 partitioned index에 대해 정리한 글입니다.

1. 파티션 인덱스

인덱스는 데이터베이스 테이블에 있는 데이터를 빨리 찾기 위한 용도의 데이터베이스 객체이며 일종의 색인기술입니다.
테이블에 있는 하나 이상의 컬럼으로 만들어지며 생성되면 인덱스 테이블(index table)을 생성해 관리되게 됩니다.

인덱스도 파티션 테이블과 같이 파티션 인덱스가 존재합니다.
파티션 인덱스는 파티션 유무에 따라 파티션과 비파티션으로 나뉘고 또 Global과 Local로 구분되게 됩니다.

 

2. 파티션 인덱스 유형

1) Local Partitioned Index / Global Partitioned Index

파티션 테이블에는 파티션키(Partition Key)가 있고 이 키로 테이블이 분할되어 있습니다.

이 파티션키와 인덱스 파티션 키가 같아 파티션된 테이블과 파티션된 인덱스가 동일하게 파티션 됐다면 Local Partitioned Index키가 달라서 다르게 파티션이 됐다면 Global Partitioned Index로 나뉘어집니다.

 

• Local 특징

1. 각 인덱스 파티션이 테이블 파티션과 1:1 대응 관계를 가지며, 테이블 파티션 속성을 그대로 상속받는다.
2. 파티션 키를 사용자가 따로 정의하지 않아도 오라클이 자동으로 관리
3. 테이블 파티션과 1:1 관계가 되도록 사용자가 수동으로 인덱스 파티션을 구성하더라도 이를 로컬 파티션 인덱스라고 부르지 않는다.
4. 테이블이 결합 파티셔닝 돼 있다면 인덱스도 같은 단위로 파티셔닝
5. 테이블 파티션 구성에 변경이 생기더라도 인덱스를 재생성할 필요가 없다.

• Global 특징

1. 테이블은 파티셔닝돼 있지 않을 수도 있다.
2. 기준 테이블의 파티션 구성에 변경(drop, exchange, split등)이 생길 때마다 인덱스가 unusable 상태로 바뀌고 그때마다 인덱스를 재생성해야 한다.
3. 9i부터 update global indexes 옵션을 주면 파티션 DDL 작업에 의해 영향을 받는 인덱스 레코드를 자동으로 갱신해 준다.

EX) ALTER TABLE 테이블명 DROP PARTITION 파티션명 UPDATE GLOBAL INDEXES;

 

파티션 테이블에서도 파티션을 하지 않은 비파티션 인덱스(Non-Partitioned Index)를 생성할 수 있는데 글로벌 비파티션 인덱스(Global Non-Partitioned Index)로도 불립니다.
비파티션 인덱스와 글로벌 파티션 인덱스를 합쳐서 글로벌 인덱스(Global Index)라고 지칭합니다.

2) Prefixed Index / Non-prefixed Index

인덱스 파티션 키 컬럼이 인덱스의 첫번째 컬럼일 때 Prefixed Index이고
첫번째 컬럼이 아니거나 아예 인덱스 컬럼에 속하지 않을 때는 Non-prefixed Index입니다.

오라클에서는 글로벌 파티션 인덱스는 Prefixed 파티션만 지원합니다.

 

3. 효용성

파티션 된 테이블에서는 대부분의 경우 Local Index가 성능이나, 가용성 및 관리의 용이성이 Global Index 보다 우수합니다.

하지만 업무의 특성이나 기타 Access의 형태에 따라 Local Index를 생성할 수 없는 경우에 Global Index를 고려합니다.

 

1) Local Prefixed Index

 

• 테이블 파티션에 대한 추가, 삭제 및 작업이 빈번할 때 관리적 측면에서 유용하다.
• 파티션 키 컬럼이 "=" 조건으로 사용될 때 유용하다.
• 파티션 키 컬럼이 Like, Between, 부등호 같은 범위검색 조건일 때 불리하다.
• 파티션 키 컬럼에 대한 검색 조건이 없으면, 인덱스의 첫번째 컬럼이 조건절에 누락된 것이므로 정상적인 사용이 불가 (Index Full Scan으로는 선택 가능)

2) Local Non-prefixed Index

• 테이블 파티션에 대한 추가, 삭제 및 작업이 빈번할 때 관리적 측면에서 유용하다.
• 파티션 키 컬럼이 조건절에 사용될 때 유용하다.
• 파티션 키 컬럼에 대한 검색 조건이 없으면 인덱스 파티션 전체를 액세스하는 비효율이 발생할 수 있다.
• 파티션 키 컬럼이 범위검색 조건으로 사용된다면 Local Prefixed 보다 유리하다. 단, 좁은 범위 검색이어야 유리하다.

3) Global Prefixed Index

• 인덱스 경합 분산에 효과적이다. 이 이유외에는 잘 사용하지 않는다.
• 여러 Local 파티션 인덱스를 액세스하는 것이 비효율적일 때 대안으로 활용 가능
• 기준 테이블 파티션의 구성에 변경이 생길 때 unusable 되므로 9i 이상이라면 update global indexes 옵션을 사용하거나 직접 rebuild 해야한다.

3) Non-partitioned Index

• OLTP 환경에서 파티션 키 컬럼이 조건절에 누락되었을 시 여러 파티션 인덱스를 액세스하므로 비효율적인데 이럴 때 비파티션 전략이 유용할 수 있다.
• NL Join(Nested Loops Join) 에서 파티션 키에 대한 넓은 범위 검색 조건을 가진 Inner 테이블 액세스 용도로 파티션 인덱스가 사용된다면 비효율적이므로 비파티션 인덱스를 고려해야한다.
• Sort 연산을 대체함으로써 부분범위 처리를 활용하고자 할 땐 비파티션 전략이 유용할 수 있다.
• 기준 테이블 파티션의 구성에 변경이 생길 때 unusable 되므로 9i 이상이라면 update global indexes 옵션을 사용하거나 직접 rebuild 해야한다.

---

1. NL Join - http://wiki.gurubee.net/pages/viewpage.action?pageId=3899794
2. OLTP(Online transaction processing) 환경 - http://develop.sunshiny.co.kr/530
3. 오라클 Index Partitioning Docs - https://docs.oracle.com/en/database/oracle/oracle-database/12.2/vldbg/index-partitioning.html#GUID-569F94D0-E6E5-45BB-9626-5506DE18FF00