파티셔닝

1. 테이블 파티셔닝

• 파티셔닝은 테이블과 인덱스 데이터를 파티션 단위로 나누어 저장하는 것을 말한다. 테이블을 파티셔닝 하면 하나의 테이블이라도 파티션 키에 따라 물리적으로는 별도의 세그먼트에 데이터가 저장되며, 인덱스도 마찬가지다. 파티셔닝은 관리적 측면에서도 성능적 측면에서도 뛰어나다.
• Range 파티셔닝은 특정 칼럼의 값의 범위에 따라 파티셔닝 하는 방식으로 주로 날짜 칼럼을 기준으로 한다.
• 해시 파티셔닝은 파티션 키에 해시 함수를 적용한 결과 값이 같은 레코드를 같은 파티션 세그먼트에 저장해 두는 방식으로 고객 ID처럼 변별력이 좋고 데이터 분포가 고른 칼럼을 파티션 기준 칼럼으로 선정한다. 해시 알고리즘 특정상 = 조건이나 In-List 조건으로 검색할 때만 파티션 Pruning이 작동한다. 파티션 키를 잘못 선정하면 데이터가 고르게 분산되지 않기 때문에 주의해야 한다. 데이터가 모든 파티션에 고르게 분산되어 있다면 병렬 I/O 성능을 극대화할 수 있으며 대용량 거래 테이블일수록 DML 발생량이 많아 경합 발생 가능성도 크기 때문에 대용량 테이블이나 인덱스에 발생하는 경합을 줄일 목적으로도 해시 파티셔닝을 사용한다. 해시 파티셔닝을 사용하면 Right Growing 인덱스의 경합 발생 가능성을 낮출 수 있다.
• 리스트 파티셔닝은 사용자에 의해 미리 정해진 그룹핑 기준에 따라 데이터를 분할 저장하는 방식이다. 대표적으로 지역분류 기준으로 파티셔닝한다. 
• 결합 파티션은 서브 파티션마다 세그먼트를 하나씩 할당하고 서브 파티션 단위로 데이터를 저장한다. 주 파티션 키에 따라 1차적으로 데이터를 분배하고 서브 파티션 키에 따라 최종적으로 저장한 위치를 결정한다. Range+해시, Range+리스트, Range+Range, 리스트+해시, 리스트+리스트, 리스트+Range 방식으로 가능하다. 
• Reference 파티션은 부모 테이블 파티션 키를 이용해 자식 테이블을 파티셔닝하는 기능이다. 
• Interval 파티셔닝은 interval 기준을 정의하여 정해진 간격으로 파티션이 자동 추가되도록 하는 기능이다. 테이블을 한 달 단위로 파티셔닝 하거나 고객 수가 일정수가 넘을 때마다 파티션이 추가되도록 할 수 있다.

2. 파티션 Pruning

• 파티션 Pruning은 하드파싱이나 실행 시점에 SQL 조건절을 분석하여 읽지 않아도 되는 파티션 세그먼트를 액세스 대상에서 제외시키는 기능이다. 파티션 키 칼럼을 상수 조건으로 조회하는 경우에 정적 파티션 Pruning이 작동하며 액세스 할 파티션이 쿼리 최적화 시점에 미리 결정된다. 바인드 변수로 조회하면 쿼리 최적화 시점에는 액세스 할 파티션을 미리 결정할 수 없기 때문에 이때는 동적 파티션 Pruning이 작동한다. NL조인할 때도 Inner 테이블이 조인 칼럼 기준으로 파티셔닝 돼 있다면 동적 파티션 Pruning이 작동한다. 동적 파티션 Pruning이 동작하면 테이블 레벨 통계가 사용되는데 정적 파티션 Pruning에서 사용되는 파티션 레벨 통계보다 다소 부정확하기 때문에 옵티마이저가 가끔 잘못된 실행계획을 수립할 수 있다.
• NL조인할 때 Inner 테이블이 조인 컬럼 기준으로 파티셔닝 돼 있다면 오라클을 Recursive 서브쿼리를 이용한 동적 파티션 Pruning을 고려한다. 드라이빙 테이블을 두 번 액세스 하기 때문에 제거될 것으로 예상되는 파티션 개수가 상당히 많고 where 조건절을 가진 드라이빙 테이블이 파티션 테이블에 비해 상당히 작을 때만 서브쿼리 Pruning이 작동한다. 
• 11g부터 파티션 테이블과 조인할 때, 블룸 필터 알고리즘을 사용해 읽지 않아도 되는 파티션을 제거해 주는 조인 필터 Pruning 또는 블룸 필터 Pruning이라 한다. 드라이빙 테이블이 클수록 조인 필터 Pruning이 유리하다. 
• Range 파티셔닝에서 월이 아닌 일자로써 파티션 키 값을 정의했다면 between 연산자를 이용해 정확한 값 범위를 주고 쿼리 해야 한다.

3. 인덱스 파티셔닝

• 각 인덱스 파티션이 테이블 파티션과 1:1 대응 관계를 가지며 테이블 파티션 속성을 그대로 상속 받는것이 로컬 파티션 인덱스이다.  파티션 키를 사용자가 따로 정의하지 않아도 오라클이 자동으로 관리해 주며 만약 테이블이 결합 파티셔닝 돼 있다면 인덱스도 같은 단위로 파티셔닝 된다. 테이블 파티션 구성에 변경이 생기더라도 인덱스를 재생성할 필요가 없어 관리 비용이 적다는 장점이 있다.
• 비파티션 인덱스는 파티셔닝하지 않은 인덱스를 말한다. 글로벌 비파티션 인덱스라고도 한다.
• 글로벌 파티션 인덱스는 테이블 파티션과 독립적인 구성을 갖도록 파티셔닝 하는 것을 말하며 테이블은 파티셔닝이 돼 있지 않을 수도 있다. 효용성이 낮고 몇몇 제약사항 때문에 잘 사용하지는 않는다. 테이블 파티션과 인덱스 파티션 간에는 항상 M:M 관계가 형성된다. 글로벌 해시 파티션 인덱스도 가능하며 Right Growing 인덱스와 같은 경합을 분산할 목적으로 주로 사용한다. 글로벌 결합 인덱스 파티셔닝은 불가능하다. 경합을 분산시키려고 글로벌 해시 파티셔닝을 하는 경우 외에는 거의 사용되지 않는다. 글로벌 파티션 인덱스에는 Prefixed 파티션만 허용되므로 날짜 칼럼을 선두에 두면 범위검색 조건으로 사용되는 경우가 많기 때문이다.
• 파티션 인덱스를 생성할 때, 파티션 키 컬럼을 인덱스 키 칼럼 왼쪽 선두에 두는 것을 Prefixed 파티션 인덱스라고 하며 파티션 인덱스를 생성할 때, 파티션 키 칼럼을 인덱스 키 칼럼 왼쪽 선두에 두지 않거나 파티션 키가 인덱스 칼럼에 속하지 않을 때를 Nonprefixed 파티션 인덱스라고 한다. 비파티션 인덱스, 글로벌 Prefixed 파티션 인덱스, 로컬 Prefixed 파티션 인덱스, 로컬 Nonprefixed 파티션 인덱스 조합이 가능하다. 
• 로컬 Nonprefixed 파티션 인덱스는 이력성 데이터를 효과적으로 관리할 수 있게 해주며 인덱스 스캔 효율성을 높이는 데에도 유리하다. 이력성 데이터는 대부분 날짜 칼럼을 파티션 키로 사용하므로 로컬 Prefixed 파티션 인덱스라면 범위검색 조건 칼럼이 선두에 와야 하기 때문에 스캔 효율이 나쁘지만 로컬 Nonprefixed 파티션 인덱스는 선두 컬럼에 계좌번호나 회원 ID처럼 = 조건 칼럼을 선두에 둘 수 있기 때문에 효율적이다.
• DW/DSS 애플리케이션에는 날짜 컬럼 기준으로 파티셔닝 된 이력성 대용량 테이블이 많기 때문에 Nonprefixed 로컬 파티션 인덱스가 성능 면에서 유리하다.
• OLTP성 애플리케이션 환경에서는 비파티션 인덱스가 대게 좋다. 만약 로컬 인덱스를 선택했다면 Prefixed 파티션이든 Nonprefixed 파티션이든 검색 조건에 항상 사용되는 칼럼을 파티션 키로 선정하는 노력이 필요하다. 
• Unique 파티션 인덱스를 정의할 때는 인덱스 파티션 키가 모두 인덱스 구성 컬럼에 포함되어야 한다.
• 글로벌 파티션 인덱스는 Prefixed 파티션만 가능하다.

 

- 참고 서적 : 오라클 성능 고도화 원리와 해법 2 -