[ORACLE] INDEX 의 모든것 (PARTITION INDEX GLOBAL LOCAL PREFIXED)
1. Partitioned Index / Non - Partitioned Index
Non-Partitioned Index는 하나의 루트 노드에서 리프 노드까지 전체적인 균형을 유지하는 구조를 갖고 있다.
반면, Partitioned Index는 각 파티션이 독립적인 루트 노드와 리프 노드를 가지며, 각 파티션이 자체적으로 균형을 유지
이로 인해 대용량 테이블에서 Global Index의 깊이는 매우 깊어질 수 있는 반면,
Partitioned Index는 파티션별로 관리되므로 인덱스의 깊이가 얕아지고,
파티션 단위로 인덱스를 관리할 수 있어 병렬 처리에 의한 인덱스 관리에 매우 효과적이다.
Partitioned Index는 검색 조건에 인덱스 파티션 키가 포함된 경우에만 해당 인덱스 파티션을 검색하여 성능을 극대화할 수 있다. 하지만, 만약 인덱스 파티션 키가 검색 조건에 포함되지 않으면, 전체 파티션에 대해 인덱스를 검색해야 하므로 성능 저하가 발생할 수 있다.
특히, Local Index의 경우 인덱스 파티션 키와 베이스 테이블의 파티션 키가 동일하므로,
검색 조건에 이 키가 포함되지 않으면 모든 파티션에서 반복적인 검색 작업이 일어나 성능이 저하될 수 있다는 것을 꼭 명심.
Global Partitioned Index는 첫 번째 컬럼(Leading Column)이 인덱스 파티션 키가 되는 Prefixed Index로만 생성된다.
따라서 조회 조건에 첫 번째 컬럼이 포함되지 않는 경우는 없다.
이로 인해 특정 인덱스 파티션을 지정해 검색할 수 있으며,
대용량 데이터 환경에서 Non-Partitioned Global Index보다 성능 면에서 우수할 수 있다.
또한, 인덱스를 재구성할 때도 각 파티션별로 병렬 처리가 가능하다는 장점이 있다.
2. Global Index와 Local Index
Global Index와 Local Index의 차이를 생각할 때, 많은 개발자들이 "Partition되었는가?
아니면 Partition 되어있지 않은가?"로 단순하게 구분하는 경우가 많다.
그러나 이 접근은 잘못되었다.
Global Index도 Partitioning이 가능하며, 파티션별로 관리될 수 있다.
핵심 차이점은 '정렬'이다.
- Global Index는 테이블 전체에 대해 Indexed Column과 Rowid 순으로 정렬이 보장된 인덱스로 오라클 옵티마이저가 정렬을 보장함
- Local Index는 테이블 전체가 아닌 각 파티션 내에서만 정렬이 보장됨. 즉, 전체 테이블 차원의 정렬은 보장되지 않음
일부 개발자들은 Local Index에 반드시 파티션 키가 포함되어야만 검색 조건에서 파티션 키가 사용될 수 있다고 생각하지만 Global Index는 'Global'이라는 이름에서 알 수 있듯이 기본적으로 Non-Partitioned Index 이다.
- 대용량 테이블에서 인덱스 관리를 효율적으로 하고 검색 성능을 향상시키기 위해 Global Index를 Partitioning 하고
Global Index는 Base Table의 파티션 키와 무관하게 Partitioning할 수 있다.
- Base Table의 파티션 키로 Global Index를 Partitioning했다 하더라도, Local Index처럼 동일한 파티션 구조를 갖는 것은 아니므로 테이블 DDL 작업 시 전체 인덱스를 Rebuild 해야 할 수도 있다.
Global Prefixed Partitioned Index는 인덱스 파티션 키(DEPTNO)가 인덱스의 첫 번째 컬럼이 된다. 이 경우 모든 인덱스 값들이 정렬되며, 각 파티션의 루트 블록에서부터 리프 블록에 이르기까지 정렬이 보장된다.
반면, Global Non-Prefixed Partitioned Index는 인덱스 파티션 키로 각 파티션의 루트 블록을 만들지 않으므로 전체 데이터에 대한 정렬이 보장되지 않으며, 현재 이 방식은 지원되지 않는다.
Global Index는 Local Index와 달리 Range Partitioning만을 지원한다. 그 이유는 '정렬' 때문.
Range Partitioning은 파티션 키 자체를 정렬에 의해 생성하므로 다른 파티션 방식과는 다르다.
3. Prefixed Index와 Non-Prefixed Index
Prefixed Index와 Non-Prefixed Index의 차이는 인덱스 파티션 키가 인덱스의 선두 컬럼으로 위치하는지 여부
- Prefixed Index: 인덱스 파티션 키가 선두 컬럼으로 위치
- Non-Prefixed Index: 인덱스 파티션 키가 선두 컬럼이 아님
Global Index는 Non-Prefixed로 생성할 수 없다. 즉, Global Index는 항상 Prefixed Index로만 존재한다.
이 때문에 Global Index는 Non-Partitioned이든 Partitioned이든 조회 시 항상 인덱스 파티션 키가 검색 조건에 포함되어야 하며, 이로 인해 파티션의 반복적인 Lookup에 의한 성능 저하가 발생하지 않는다.
반면, Local Index는 인덱스 컬럼에 파티션 키가 포함되었는지와 상관없이 검색 조건에 파티션 키가 포함되지 않을 수 있다. 이 경우, 파티션을 반복적으로 검색해야 하므로 성능 저하가 발생할 수도 있다.