3. 인덱스 파티셔닝
1. 인덱스 파티션 유형
인덱스는 파티션 인덱스와 비파티션 인덱스로 나뉘고 파티션 인덱스는 로컬과 글로벌로 나뉜다.
-
비파티션 인덱스
-
파티션 인덱스
-
글로벌 파티션 인덱스
-
로컬 파티션 인덱스
-
파티션이 안된 비파티션 테이블은 비파티션 인덱스와 글로벌 파티션 인덱스를 가질 수 있다.
파티션된 파티션 테이블은 모든 인덱스가 비파티션 인덱스 및 모든 파티션 인덱스를 가질 수 있다.
2. 로컬 파티션 인덱스
로컬 파티션 인덱스는 각 인덱스 파티션이 테이블 파티션과 1:1 대응 관계를 가지며, 테이블 파티션 속성을 그대로 상속받는다.
파티셔닝을 전체로 하기 때문에 '로컬 인덱스' 라고도 부른다.
로컬 파티션 인덱스는 항상 파티션 테이블과 1:1 관계를 형성하므로 만약 테이블이 결합 파티셔닝돼 있다면 인덱스도 같은 단위로 파티셔닝된다.
로컬파티션 인덱스가 갖는 장점은 무엇보다 관리적 편의성에 있다.
=> 테이블 파티션에 변경이 생겨도 오라클이 알아서 인덱스 파티션과의 1:1 관계를 맞춰준다.
3. 비파티션 인덱스
비파티션 인덱스는 말 그대로 파티션하지 않은 인덱스를 말한다.
비파티션 인덱스와 비파티션 테이블은 1:1 관계지만, 파티션 테이블과는 1:M 관계를 갖는다.
즉, 테이블 파티션과는 하나의 인덱스 세그먼트가 여러 테이블 파티션 세그먼트와 관계를 갖는다.
그런 의미에서 비파티션 인덱스를 '글로벌 비파티션 인덱스' 라고도 부른다.
비파티션 인덱스는 기준 테이블의 파티션 구성이 변경되면 그때마다 인덱스가 unusable 상태로 바뀌고 인덱스를 재생성해줘야 한다.
4. 글로벌 파티션 인덱스
글로벌 파티션 인덱스는 테이블 파티션과 독립적인 구성을 갖도록 파티셔닝하는 것을 말한다.
글로벌 파티션 인덱스도 비파티션 인덱스와 같이 기준 테이블의 파티션 구성이 변경되면 그때마다 인덱스가 unusable 상태로 바뀌고 인덱스를 재생성해줘야 한다.
✅테이블 파티션과의 관계
인덱스를 테이블 파티션과 같은 키 컬럼으로 글로벌 파티셔닝한다면 파티션 기준 값을 어떻게 정의하느냐에 따라 1:M, M:1, M:M 관계가 모두 가능하다. (본질은 M:M)
즉, 하나의 인덱스 파티션이 여러 테이블 파티션과 관계를 갖고, 반대로 하나의 파티션 테이블이 여러 인덱스 파티션과 관계를 갖는다.
인덱스를 테이블 파티션과 다른 키 컬럼으로 글로벌 파티셔닝할 수도 있는데, 이때는 항상 M:M 관계이다.
✅ 글로벌 해시 파티션 인덱스
9i까지는 글로벌 Range 파티션 인덱스만 가능했지만 10g부터는 글로벌 해시 파티션 인덱스도 가능해졌다.
즉, 테이블과 독립적으로 인덱스만 해시 키 값에 따라 파티셔닝할 수 있게 되었다.
글로벌 해시 파티션 인덱스는 Right Growing 인덱스 처럼 Hot 블록이 발생하는 인덱스의 경합을 분산할 목적으로 주로 사용된다.
글로벌 결합 인덱스 파티셔닝은 불가능하다.
5. Prefixed vs NonPrefixed
1. Prefixed
파티션 인덱스를 생성할 때, 파티션 키 컬럼을 인덱스 키 컬럼 왼쪽 선두에 두는 것을 말한다.
2. NonPrefixed
파티션 인덱스를 생성할 때, 파티션 키 컬럼을 인덱스 키 컬럼 왼쪽 선두에 두지 않는 것을 말한다.
파티션 키가 인덱스 컬럼에 아예 속하지 않을 때도 여기에 속한다.
로컬과 글로벌, Prefixed와 Nonprefixed를 조합하면 4가지 파티션 인덱스가 나온다. (조합 3 + 비파티션)
✅비파티션 인덱스
제약 없음
✅글로벌 Prefixed 파티션 인덱스
테이블 파티션 키와 인덱스 파티션 키가 같아도 되고 다를 수 있다.
인덱스 선두 컬럼에는 인덱스 파티션 키가 와야 된다.
✅로컬 Prefixed 파티션 인덱스
테이블 파티션 키와 인덱스 파티션 키가 같아야 한다.
인덱스 선두 컬럼에는 인덱스 파티션 키가 와야 한다.
✅로컬 NonPrefixe 파티션 인덱스
테이블 파티션 키와 인덱스 파티션 키가 같아야 한다.
인덱스 선두 컬럼에는 인덱스 파티션 키가 올 수 없다.
Unique 파티션 인덱스를 만들 때는 파티션 키 컬럼이 인덱스 컬럼에 반드시 포함돼 있어야 한다.
비파티션 인덱스는 제약이 없어서 상관없다.
6. 글로벌 파티션 인덱스의 효용성
글로벌 파티션 인덱스는 경합을 분산시키려고 글로벌 해시 파티셔닝하는 외에는 거의 사용되지 않는다.
비파티션 테이블에 대한 글로벌 파티션 인덱스는 테이블을 파티셔닝하지 않을 정도로 중소형급 테이블이면 굳이 인덱스만을 따로 파티셔닝 할 이유는 별로 없다.
파티션 테이블에 대한 글로벌 파티션 인덱스도 글로벌 파티션 인덱스보단 로컬 파티션 인덱스가 주로 사용되고 있다. (관리의 편의성, 인덱스 높이 조절 측면)
NL 조인에서 넓은 범위 조건을 가지고 Inner 테이블 액세스를 위해 자주 사용된다면 비파티션 인덱스가 가장 좋은 선택이다.
7. 로컬 Nonprefixed 파티션 인덱스의 효용성
로컬 Nonprefixed 파티션 인덱스는 이력성 데이터를 효과적으로 관리할 수 있게 해주고, 인덱스 스캔 효율성을 높이는 데에도 유리하다.
인덱스는 등치조건을 선두 컬럼에 두고 between같은 범위 조건 컬럼을 뒤쪽에 위치시켜야 한다.
그런 측면에서 로컬 Nonprefixed는 범위 조건 컬럼을 앞쪽에 위치시키지 않아도 되기 때문에 각 인덱스 파티션마다 필요한 최소 범위만 스캔하고 멈출 수 있다.
✅ 글로벌 Prefixed 파티션 인덱스와의 비교
글로벌 파티션 인덱스는 Prefixed 파티션만 허용되므로 거래일자처럼 범위검색 조건으로 자주 사용되는 컬럼이 선두일 때 로컬 Prefixed 파티션과 마찬가지로 인덱스 스캔 효율이 나쁘다.
또한, 과거 파티션을 제거하고 신규 파티션을 추가해야하는 관리적 부담이 크다.
✅ 비파티션 인덱스와의 비교
비파티션 인덱스를 이용하더라도 관리적 부담은 글로벌 파티션과 동일하게 발생한다.
비파티션 인덱스는 병렬 쿼리를 허용하지 않아, 이때 로컬 Nonprefixed 파티션 인덱스라면 여러 병렬 프로세스가 각각 하나의 인덱스 세그먼트를 스캔하도록해 쿼리의 응답속도를 향상 시킬 수 있다.
8. 액세스 효율을 고려한 인덱스 파티셔닝 선택 기준
✅ DW성 애플리케이션 환경
DW/DSS 애플리케이션에는 날짜 컬럼 기준으로 파티셔닝된 이력성 대용량 테이블이 많다.
따라서 관리적 측면뿐만 아니라 병렬 쿼리 활용 측면에서도 로컬 파티션 인덱스가 좋은 선택이다.
로컬 인덱스 중에는 Nonprefixed 파티션 인덱스가 성능적으로 유리할 때가 많다.
✅ OLTP성 환경
OLTP성 환경에서는 비파티션 인덱스가 대게 좋은 선택이다.
만약 테이블이 파티셔닝돼 있다면 인덱스 파티셔닝을 고려할 수 있는데, 특히 로컬 파티션 인덱스는 테이블 파티션에 대한 DDL 작업 후 인덱스를 재생성하지 않아도 돼 가용성 측면에서 유리하다.
OLTP 환경에서 로컬 파티션 인덱스를 선택했다면 Prefixed 파티션이든 Nonprefixed 파티션이든 검색 조건에 항상 사용되는 컬럼을 파티션 키로 선정하려고 해야 한다.
9. 인덱스 파티셔닝 제약을 고려한 데이터베이스 설계
중요한 제약 두 가지
-
Unique 파티션 인덱스를 정의할 때는 인덱스 파티션 키가 모두 인덱스 구성 컬럼에 포함돼야 한다.
-
글로벌 파티션 인덱스는 Prefixed 파티션이어야 한다.
