파티셔닝

파티셔닝 : 테이블 또는 인덱스 데이터를 파티션 단위로 나눠 저장하는 것.

테이블 또는 인덱스를 파티셔닝하면 파티션 키에 따라 물리적으로는 별도의 세그먼트에 데이터를 저장한다.

* 관리적 측면 : 파티션 단위 백업, 추가, 삭제, 변경

* 성능적 측면 : 파티션 단위 조회 및 DML 수행, 경합 및 부하 분산

 

파티셔닝은 우선 관리 측면에서 많은 이점이 있다. 보관주기가 지난 데이터를 별도 장치에 백업하고 지우는 은 데이터베이스 관리자들의 일상적인 작업이다. 만약 파티션 없이 대용량 테이블에 이런 작업을 수행하려면 시간도 오래 걸리고 비효율적이다.  대용량 테이블에 인덱스를 새로 생성하거나 재생성할 때도 파티션 기능을 이용하면 효과적이다.

성능 측면에서 효용성도 매우 좋다. 데이터를 빠르게 검색할 목적으로 다양한 저장구조나 검색 기법들이 개발되고 있지만, 인덱스와 테이블 전체를 스캔하는 방법에서 크게 벗어나지 않는다. 인덱스를 이용한 랜덤 액세스 방식은 일정량이 넘어가면 Table Full Scan 보다 성능이 떨어진다. 그렇다고 초대용량 테이블을 Full Scan 하는 것은 매우 비효율적이다.

이런 경우 테이블을 파티션 단위로 나눠 관리하면 Full Table Scan 이더라도 일부 세그먼트만 읽고 작업을 마칠 수 있다.

테이블이나 인덱스를 파티셔닝하면 DBMS 는 내부에 2개 이상 (생성 초기에 하나일 수 있으나 계속 하나를 유지하면 파티셔닝 불필요) 의 저장영역을 생성하고, 그것들이 논리적으로 하나의 오브젝트임을 메타 정보로 관리한다.

파티션되지 않은 일반 테이블일 때는 테이블과 저장영역이 1:1 관계지만 파티션 테이블일 때는 1:M 관계다. 인덱스를 파티셔닝할 때도 마찬가지다. 

 

2. 파티션 유형 

가. Range 파티셔닝 

- 파티션 키 값의 범위로 분할

- 파티셔닝의 가장 일반적인 형태이며, 주로 날짜 컬럼을 기준으로 함 (예, 판매 데이터를 월별로 분할)

 

나. Hash 파티셔닝

- 파티션 키 값에 해시 함수를 적용하고, 거기서 반환된 값으로 파티션 매핑

- 데이터가 모든 파티션에 고르게 분산되도록 DBMS가  관리 -> 각 로우의 저장 위치 예측 불가 

- 파티션 키의 데이터 분포가 고른 컬럼이어야 효과적 -> 고객번호/주문일련번호 등..

- 병렬처리 시 성능효과 극대화

- DML 경합 분산에 효과적

 

다. List 파티셔닝 

- 불연속적인 값의 목록을 각 파티션에 지정

- 순서와 상관없이, 사용자가 미리 정한 기준에 따라 데이터를 분할 저장 -> 판매 데이터를 지역별로 분할 

 

라. Composite 파티셔닝

- Range 나 List 파티션 내에 또 다른 서브 파티션(Range, Hash, List ) 구성

- Range 나 List 파티션이 갖는 이점 + 각 서브파티션의 구성 이점

 

오라클에서 range 파티셔닝 하는 방법

create table 주문 (주문번호 number, 주문일자 varchar, 고객id varchar2, )
partition by range(주문일자)(
    partition p2009_q1 values less than('20090401')
    partition p2009_q2 values less than('20090701')
    partition p2010_q3 values less than('20091001')
    partition p9999_mx values less than(MAXVALUE)
);

 

[Range + Hash] 로 파티셔닝 하는 방법

create table 주문 (주문번호 number, 주문일자 varchar, 고객id varchar2, )
partition by range(주문일자)
subpartition by hash(고객id) subpartitions 8(
    partition p2009_q1 values less than('20090401')
    partition p2009_q2 values less than('20090701')
    partition p2010_q3 values less than('20091001')
    partition p9999_mx values less than(MAXVALUE)
);

 

---

3. 파티셔닝 Pruning (pruning : 가지치기)

옵티마이저가 SQL 의 대상 테이블과 조건절을 분석해 불필요한 파티션을 액세스 대상에서 제외하는 기능을 말한다.

이를 통해 액세스 조건과 관련된 파티션만 작업을 수행할 수 있다.  파티션 테이블에 조회나 DML 을 수행할 때 극적인 성능 개선의 핵심 원리가 바로 파티션 Pruning 에 있다.

기본 파티션 Pruning 에는 정적 Pruning 과 동적 Pruning 이 있다. DBMS 별로 서브쿼리 Pruning , 조인 필터 Prunning 같은 고급 Pruning 기법을 지원한다. 

 

가. 정적 파티션 Pruning 

액세스할 파티션을 컴파일 시점에 미리 결정하며, 파티션 키 컬럼을 상수 조건으로 조회하는 경우에 정적 Pruning 이 작동한다. 

 

나. 동적 파티션 Pruning

액세스할 파티션을 실행시점에 결정하며, 파티션 키 컬럼을 바인드 변수로 조회하는 경우가 대표적이다.

NL 조인할 때도 Inner 테이블이 조인 컬럼 기준으로 파티셔닝돼 있으면 동적 Pruning 작동한다.

파티션 Pruning 은 SQL 에 사용한 조건절과 파티션 구성을 분석해 DBMS 가 지능적으로 수행하는 매커니즘이므로 사용자가 굳이 신경쓰지 않아도 된다. 다만 파티션 키 컬럼에 대한 가공이 발생하지 않도록 주의해야한다. 

사용자가 명시적으로 파티션 키 컬럼을 가공했을 때는 물론, 데이터 타입이 묵시적으로 변환될 때도 정상적인 Pruning 이 불가능해지기 때문이다. 인덱스 컬럼을 조건절에서 가공하면 해당 인덱스를 사용할 수 없게 되는 것과 같은 이치다.

 

---

4. 인덱스 파티셔닝 

가. Local 파티션 인덱스 vs Global 파티션 인덱스

- Local 파티션 인덱스 : 테이블 파티션과 1:1 로 대응되도록 파티셔닝한 인덱스.

인덱스 파티션 키를 사용자가 따로 지정하지 않으며, 테이블과 1:1 관계를 유지하도록 DBMS 가 자동으로 관리해줌. 

SQL server 에선 '정렬된 파티션 인덱스'라고 부름

- Global 파티션 인덱스 : 테이블 파티션과 독립적인 구성을 갖도록 파티셔닝한 인덱스. 

SQL server 에선 '정렬되지 않은 파티션 인덱스'라고 부름

나. Prefixed 파티션 인덱스 vs. NonPrefixed 파티션 인덱스

인덱스 파티션 키 컬럼이 인덱스 구성상 왼쪽 선두 컬럼에 위치하는지에 따른 구분이다.

- Prefixed : 파티션 인덱스를 생성할 때, 파티션 키 컬럼을 인덱스 키 컬럼 왼쪽 선두에 두는 것을 말한다.

- NonPrefixed : 파티션 인덱스를 생성할 때, 파티션 키 컬럼을 인덱스 키 컬럼 왼쪽 선두에 두지 않는 것을 말한다.

파티션 키가 인덱스 컬럼에 아예 속하지 않을 때도 여기 속한다.

 

Local과  Global , Prefixed와 NonPrefixed 를 조합해 아래 4가지 구성을 만들 수 있다.

	Prefixed	NonPrefixed
Local	1	2
Global	3	4

비파티션까지 포함하면 인덱스를 총 5가지 유형으로 구분할 수 있다.

- Local Prefixed , Local NonPrefixed , Global Prefixed, Global NonPrefixed , NonPartitioned 

이 중 오라클에서는  Global NonPrefixed 를 허용하지 않는다. 

 

다.  인덱스 파티셔닝 가이드

인덱스 파티션은 파티션 테이블과 마찬가지로 성능, 관리 편의성, 가용성, 확장성을 제공하나.

테이블에 종속적인 Local 파티션과 , 테이블과 독립적인 Global 파티션 모두 가능하지만 , 

관리적은 측면에서는 Local 인덱스가 훨씬 유용하다.

테이블 파티션에 대한 Drop, Exchange, Split 등의 작업 시 Global 인덱스는 Unusable 상태가 되기 때문이다.

인덱스를 다시 사용할 수 있게 하려면 인덱스를 ReBuild 하거나 재생성해 주어야 한다.

 

구분	적용기준 및 고려사항
비파티션	* 파티션 키 컬럼이 조건절에 없으면 여러 인덱스 파티션을 액세스 하므로 비효율적 특히 OLTP 환경에서 성능에 미치는 영향이 크므로 비파티셔닝 전략이 유용할 수 있음  * NL 조인에서 파티션 키에 대한 넓은 범위검색 조건을 가지고 Inner 테이블 액세스 용도로 인덱스 파티션을 사용하면 비효율적이다. -> 비파티션 인덱스 사용 고려  * 파티션 인덱스를 이용하면 sort order by 대체 효과 상실. 소트 연산을 대체함으로써 부분범위처리를 활용하고자 할 땐 비파티셔닝 전략이 유용 * 테이블 파티션 이동, 삭제 등의 작업 시 unusable 되므고 적용 시 주의
Global Prefixed	* 인덱스 경합 분산에 효과적 * 여러 Local 인덱스 파티션을 액세스하는 것이 비효율적일 때 대안으로 활용 가능 * 테이블 파티션 이동, 삭제 등의 작업 시 unusable 되므로 적용 시 주의
Local Prefixed	* 관리적 측면에서 유용 : 테이블 파티션에 대한 추가, 삭제 등의 작업이 빈번할 때 * 이력성 데이터를 주로 관리하는 DB 환경에 효과적 * 파티션 컬럼이 '=' 조건으로 사용될 때 유용 * 파티셔닝 컬럼이 검색 조건에 없으면 인덱스 선두 컬럼이 조건절에 누락된 것이므로 정상적인 사용이 불가 (Index Full Scan 으로는 선택가능) * 파티션 키 컬럼(=인덱스 선두 컬럼) 이 Like , Between, 부등호 같은 범위검색 조건일 때 불리
Local Non Prefixed	* 관리적 측면에서 유용 : 테이블 파티션에 대한 추가, 삭제 등의 작업이 빈번할 때 * 이력성 데이터를 주로 관리하는 DB 환경에 효과적 * 파티션 키 컬럼이 조건절에 사용될 때 유용 * 파티셔닝 컬럼에 대한 검색 조건이 없으면 인덱스 파티션 전체를 액세스하는 비효율이 발생할 수 있으므로 주의  * 파티션 키 컬럼이 범위검색 조건으로 자주 사용된다면 Local Prefixed 보다 Local NonPrefixed 가 유리. 그렇더라도 좁은 범위 검색이어야 함.

 

---

출처

SQL 전문가 가이드 - 한국데이터산업진흥원