[1] 파티션 개요

(1) 파티셔닝(Partitioning)

• 또는 인덱스 데이터를 파티션(Partition) 단위로 나누어 저장하는 것이다.
• 테이블을 파티셔닝하면 파티션 키에 따라 물리적으로는 별도의 세그먼트에 데이터를 저장하며, 인덱스도 마찬가지이다.
• 파티션되지 않은 일반 테이블일 때는 테이블과 저장영역(Oracle의 세그먼트)이 1:1 관계지만 파티션 테이블일 때는 1:M 관계다.
• 인덱스를 파티셔닝할 때도 마찬가지다.

(2) 파티셔닝이 필요한 이유

• 관리적 측면 : 파티션 단위 백업, 추가, 삭제, 변경
  • 보관주기가 지난 데이터를 별도 장치에 백업하고 지우는 일은 데이터베이스 관리자들의 일상적인 작업이다.
  • 만약 파티션 없이 대용량 테이블에 이런 작업들을 수행하려면 시간도 오래 걸리고 비효율적이다.
• 성능적 측면 : 파티션 단위 조회 및 DML 수행, 경합 및 부하 분산
  • 대용량 테이블에 인덱스를 새로 생성하거나 재생성할 때도 파티션 기능을 이용하면 효과적이다.
  • 데이터를 빠르게 검색할 목적으로 데이터베이스마다 다양한 저장구조와 검색 기법들이 개발되고 있지만, 인덱스를 이용하는 방법과 테이블 전체를 스캔하는 두 가지 방법에서 크게 벗어나지는 못하고 있다.
  • 인덱스를 이용한 Random 액세스 방식은 일정량을 넘는 순간 Full Table Scan보다 오히려 성능이 나쁘다.
  • 그렇다고 초대용량 테이블을 Full Scan 하는 것은 매우 비효율적이다.
  • 테이블을 파티션 단위로 나누어 관리하면, Full Table Scan이라 하더라도 일부 세 그먼트만 읽고 작업을 마칠 수 있다.
  • 테이블이나 인덱스를 파티셔닝하면 DBMS는 내부적으로 2개 이상(생성 초기에 하나일 수는 있으나 계속 하나를 유지한다면 파티셔닝은 불필요)의 저장영역을 생성하고, 그것들이 논리적으로 하나의 오브젝트임을 메타정보로 관리한다.

[2] 파티션 유형

(1) Range 파티셔닝

• 파티션 키 값의 범위(Range)로 분할한다.
• 파티셔닝의 가장 일반적인 형태이다.
• 주로 날짜 칼럼을 기준으로 한다.
• 예시) 판매 데이터를 월별로 분할

(2) Hash 파티셔닝

• 파티션 키 값에 해시 함수를 적용하고, 거기서 반환된 값으로 파티션을 매핑한다.
• 데이터가 모든 파티션에 고르게 분산되도록 DBMS가 관리한다,
• 각 로우의 저장 위치 예측이 불가하다.
• 파티션 키의 데이터 분포가 고른 칼럼이어야 효과적이다.
• 예시) 고객번호, 주문일련번호 등
• 병렬처리 시 성능 효과가 극대화된다.
• DML 경합 분산에 효과적이다.

(3) List 파티셔닝

• 순서와 상관없이, 사용자가 미리 정한 그룹핑 기준에 따라 데이터를 분할 저장한다.
• 불연속적인 값의 목록을 각 파티션에 지정한다.
• 예시) 판매 데이터를 지역별로 분할

(4) Composite 파티셔닝

• Range나 List 파티션 내에 또 다른 서브 파티션(Range, Hash, List) 구성하는 것이다.
• Range + List 또는 List + Hash 등
• Range나 List 파티션이 갖는 이점 + 각 서브 파티션 구성의 이점

[3] 파티션 Pruning

• 옵티마이저가 SQL의 대상 테이블과 조건절을 분석하여 불필요한 파티션을 액세스 대상에서 제외하는 기능이다.
• 액세스 조건과 관련된 파티션에서만 작업을 수행할 수 있게 된다.
• 파티션 테이블에 조회나 DML을 수행할 때 극적인 성능 개선을 가져다 주는 핵심 원리이다.
• 정적(Static) 파티션 Pruning
  • 액세스할 파티션을 컴파일 시점에 미리 결정한다.
  • 파티션 키 칼럼을 상수 조건으로 조회하는 경우에 작동한다.
• 동적(Dynamic) 파티션 Pruning
  • 액세스할 파티션을 실행 시점에 결정한다.
  • 파티션 키 칼럼을 바인드 변수로 조회하는 경우가 대표적이다.
  • NL Join할 때도 Inner 테이블이 조인 칼럼 기준으로 파티셔닝 돼 있으면 동적 Pruning이 작동한다.
• ⭐️ 주의할 점
  • 파티션 키 칼럼에 대한 가공이 발생하지 않도록 주의해야 한다.
  • 사용자가 명시적으로 파티션 키 칼럼을 가공했을 때는 물론, 데이터 타입이 묵시적으로 변환될 때도 정상적인 Pruning이 불가능해지기 때문이다.
  • 인덱스 칼럼을 조건절에서 가공하면 해당 인덱스를 사용할 수 없게 되는 것과 같은 이치다.

[4] 인덱스 파티셔닝

(1) Local 파티션 인덱스 vs. Global 파티션 인덱스

• Local 파티션 인덱스
  • 테이블 파티션과 1:1로 대응되도록 파티셔닝한 인덱스
  • 인덱스 파티션 키를 사용자가 따로 지정하지 않으며, 테이블과 1:1 관계를 유지하도록 DBMS가 자동으로 관리한다.
• Global 파티션 인덱스
  • 테이블 파티션과 독립적인 구성을 갖도록 파티셔닝한 인덱스

(2) Prefixed 파티션 인덱스 vs. NonPrefixed 파티션 인덱스

• 인덱스 파티션 키 칼럼이 인덱스 구성상 왼쪽 선두 칼럼에 위치하는지에 따른 구분
  • Prefixed
    • 파티션 인덱스를 생성할 때, 파티션 키 칼럼을 인덱스 키 칼럼 왼쪽 선두에 두는 것.
  • Nonprefixed
    • 파티션 인덱스를 생성할 때, 파티션 키 칼럼을 인덱스 키 칼럼 왼쪽 선두에 두지 않는 것.
    • 파티션 키가 인덱스 칼럼에 아예 속하지 않을 때도 여기에 속한다.

(3) 인덱스 파티셔닝 가이드

• 비파티션까지 포함에 인덱스를 총 5가지 유형으로 구분된다.
• 관리적인 측면에서는 Local 인덱스가 훨씬 유용하다.
• ⭐️ 테이블 파티션에 대한 Drop, Exchange, Split 등의 작업 시 Global 인덱스는 Unusable 상태가 되기 때문이다.
• 인덱스를 다시 사용할 수 있게 하려면 인덱스를 Rebuild 하거나 재생성해 주어야 한다.

1. Local Prefixed 파티션 인덱스

• 관리적 측면에서 유용하다.
  • 테이블 파티션 추가, 삭제 등 작업이 빈번할때, 이력성 데이터를 주고 관리하는 DB에서는 효과적이다.
• 파티션 키 컬럼이 '=' 조건으로 사용될 때 유용하다.
• 파티셔닝 컬럼에 대한 검색 조건이 없으면 인덱스 선두 컬럼이 조건절이 누락된 것이므로 정상적으로 사용이 불가하다. (index Full scan 선택 가능)
• 파티션 키 컬럼 (= 인덱스 선두 컬럼) like , between, 부등호 같은 범위검색 조건일 때 불리하다.

2. Local NonPrefixed 파티션 인덱스

• 관리적 측면에서 유용하다.
  • 테이블 파티션 추가, 삭제 등 작업이 빈번할때 이력성 데이터를 주고 관리하는 DB에서는 효과적이다.
• 파티션 키 컬럼이 조건으로 사용될 때 유용하다.
• 파티셔닝 컬럼에 대한 검색 조건이 없으면 인덱스 파티션 전체를 엑세스 하는 비효율이 발생 할수 있으므로 주의한다.
• 파티션 키 컬럼이 범위검색 조건으로 자주 사용된다면 Local Prefixed 보다 Local NonPrefiexd 가 유리하다. , 그렇더라도 좁은 범위 검색이어야 한다.

3. Global Prefixed 파티션 인덱스

• Global 파티션 인덱스 : 테이블 파티션과 독립적인 구성을 갖도록 파티셔닝한 인덱스
• 인덱스 경합 분산에 효과적이다.
• 여러 Local 인덱스 파티션을 액세스하는 것이 비효율적일 때 대안으로 활용 가능하다.
• 테이블 파티션 이동, 삭제 등 작업시 unusable 되므로 적용 시 주의한다.

4. Global NonPrefixed 파티션 인덱스

• Oracle은 지원하지 않는다.

5. 비파티션(NonPartitioned) 인덱스

• 파티션 키 컬럼 조건절에 누락되면 여러 인덱스 파티션을 액세스해야 하므로 비효율적이다.
• 특히 OLTP 환경에서는 성능에 미치는 영향이 크므로 비파티셔닝 전략이 유용할 수 있다.
• NLJoin 에서 파티션키에 대한 넓은 범위조건을 가지고 inner 테이블 액세스 용도로 인텍스 파티션을 사용된다면 비효율적이다.
  • 비파티셔닝 인덱스 사용을 고려한다.
• 파티션 인덱스를 이용하면 sort order by 대체 효과가 상실된다.
• 소트연산을 대체함으로써 부분범위처리를 활용하고자 할 때는 비파티셩닝 전략이 유용하다.
• 테이블 파티션 이동, 삭제 등 작업시 unusable 되므로 적용 시 주의한다.