01 테이블 파티셔닝
테이블과 인덱스 데이터를 파티션 단위로 나누어 저장하는 것
* 파티셔닝이 필요한 이유
- 관리적 측면: 파티션 단위 백업, 추가, 삭제, 변경
- 성능적 측면: 파티션 단위 조회 및 DML 수행
클러스터와 다른 점은 세그먼트 단위로 모아서 저장한다는 것.
클러스터는 블록 단위로 데이터를 모아 저장한다.
IOT와 파티셔닝을 조합함으로 놀라운 성능 효과를 얻을 수 있다.
테이블 파티션과 인덱스 파티션은 구분돼야 한다.
(1) 파티션 기본 구조
* 수동 파티셔닝
뷰틀 통해 파티션 기능을 구현
파티션 뷰의 핵심 기능은 뷰 쿼리에 사용된 조건절에 부합하는 테이블만 읽는다
* 파티션 테이블
파티션 테이블 기능을 이용하면 훨씬 간편하게 파티션을 정의할 수 있을 뿐 아니라 기능적으로 더 낫다
(2) Range 파티셔닝
8버전부터 제공된 가장 기초적인 파티셔닝 방식으로서, 주로 날짜 컬럼을 기준으로 한다.
파티션 키로는 하나 이상의 컬럼을 지정할 수 있고, 최대 16개까지 허용된다.
maxvalue 파티션을 반드시 생성해 두는 것이 좋다.
(3) 해시 파티셔닝
파티션 키에 해시 함수를 적용한 결과 값이 같은 레코드를 같은 파티션 세그먼트에 저장해 두는 방식
주로 고객ID 처럼 변별력이 좋고 데이터 분포가 고른 컬럼을 파티션 기준 컬럼으로 선정해야 효과적이다.
* 병렬 쿼리 성능 향상
데이터가 모든 파티션에 고르게 분산돼 있다면, 더구나 각 파티션이 서로 다른 디바이스에 저장돼 있다면 병렬 I/O 성능을 극대화할 수 있다.
* DML 경합 분산
병렬 쿼리 서능 향상 뿐 아니라 동시 입력이 많은 대용량 테이블이나 인덱스에 발생하는 경합을 줄일 목적으로도 해시 파티셔닝을 사용한다.
(4) 리스트 파티셔닝
사용자에 의해 미리 정해진 그룹핑 기준에 따라 데이터를 분할 저장하는 방식
(5) 결합 파티셔닝
결합파티셔닝을 구성하면 서브 파티션마다 세그먼트를 하나씩 할당하고, 서브 파티션 단위로 데이터를 저장한다.
즉, 주 파티션 키에 따라 1차적으로 데이터를 분배하고, 서브 파티션 키에 따라 최종적으로 저장할 위치를 결정한다.
* Range + 해시 결합 파티셔닝
* Range + 리스트 결합 파티셔닝
* 기타 결합 파티셔닝
(6) 11g 에 추가된 파티션 유형
* Reference 파티셔닝
부모 테이블 파티션 키를 이용해 자식 테이블을 파티셔닝 하는 기능
* Interval 파티셔닝
테이블을 일 단위로 파티셔닝 했을 때 유용하다.
02 파티션 Pruning
하드파싱이나 실행 시점에 SQL 조건절을 분석하여 읽지 않아도 되는 파티션 세그먼트를 액세스 대상에서 제외시키는 기능
파티션 테이블에 대한 쿼리나 DML을 수행할 때 극적인 성능 개선을 가져다 주는 핵심 원리가 Pruning에 있다고 할 수 있다.
(1) 기본 파티션 Pruning
- 정적 파티션 Pruning: 파티션 키 컬럼을 상수 조건으로 조회하는 경우에 작동하며, 액세스할 파티션이 쿼리 최적화 시점에 미리 결정되는 것이 특징이다.
- 동적 파티션 Pruning: 파티션 키 컬럼을 바인드 변수로 조회하면 쿼리 최적화 시점에는 액세스할 파티션을 미리 결정할 수 없다.
* 파티션 Pruning 기능에 따른 실행계획 비교
* 파티션 Pruning 기능에 따른 I/O 수행량 비교
* 동적 파티션 Pruning 시 테이블 레벨 통계 사용
(2) 서브 쿼리 Pruning
조인에 사용되는 고급 파티션 Pruning 기법
- 서브쿼리 Pruning
- 조인필터 Pruning
(3) 조인필터 Pruning
* 블룸 필터 (Bloom Filter) 알고리즘
* 블룸 필터 알고리즘에서 false positive 를 줄이는 방법
* 조인 필터 (=블룸 필터) Pruning
11g부터 적용되기 시작한 이 기능은 파티션 테이블과 조인할 때,
읽지 않아도 되는 파티션을 제거해주는 것으로서
'조인 필터 Pruning' 또는 '블룸 필터 Pruning'이라고 부른다.
(4) SQL 조건절 작성 시 주의사항
03 인덱스 파티셔닝
(1) 인덱스 파티션 유형
* 인덱스 파티션 유형
- 비파티션 테이블
- 파티션 테이블
- 비파티션 인덱스
- 글로벌 파티션 인덱스
- 로컬 파티션 인덱스
글로벌 인덱스 = 비파티션 인덱스 + 글로벌 파티션 인덱스
- 비파티션 테이블은 비파티션 인덱스와 글로벌 파티션 인덱스를 가질 수 있다.
- 파티션 테이블은 비파티션 인덱스, 글로벌 파티션 인덱스, 로컬 파티션 인덱스를 가질 수 있다.
(2) 로컬 파티션 인덱스
각 파티션이 테이블 파티션과 1:1 대응 관계를 가지며, 테이블 파티션 속성을 그대로 상속받는다.
파티셔닝을 전제로 하므로 흔히 '로컬 인덱스' 라고 줄여서 부른다.
로컬 파티션 인덱스가 갖는 장점은 무엇보다 관리적 편의성에 있다.
테이블 파티션 구성에 변경이 생기더라도 인덱스를 재생성할 필요가 없어 관리 비용이 아주 적다.
(3) 비파티션 인덱스
하나의 인덱스 세그먼트가 여러 테이블 파티션 세그먼트와 관계를 갖는다.
'글로벌 비파티션 인덱스'
(4) 글로벌 파티션 인덱스
테이블 파티션과 독립적인 구성을 갖도록 파티셔닝 하는 것
* 테이블 파티션과의 관계
* 글로벌 해시 파티션 인덱스
(5) Prefixed vs. Nonprefixed
- Prefixed: 파티션 인덱스를 생성할 때, 파티션 키 컬럼을 인덱스 키 컬럼 왼쪽 선두에 두는 것
- Nonprefixed: 파티션 인덱스를 생성할 때, 파티션 키 컬럼을 인덱스 키 컬럼 왼쪽 선두에 두지 않는 것
글로벌 파티션 인덱스
- 비파티션 인덱스
- 글로벌 Prefixed 파티션 인덱스
- 로컬 Prefixed 파티션 인덱스
- 로컬 Nonprefixed 파티션 인덱스
(6) 파티션 인덱스 구성 예시
* 인덱스 파티셔닝 예제
(7) 글로벌 파티션 인덱스의 효용성
경합을 분산시키려고 글로벌 해시 파티셔닝하는 경우 외에는 거의 사용하지 않는 실정
1. 테이블과 같은 컬럼으로 파티셔닝 하는 경우
2. 테이블과 다른 컬럼으로 파티셔닝 하는 경우
(8) 로컬 Nonprefixed 파티션 인덱스의 효용성
이력성 데이터를 효과적으로 관리할 수 있게 해주고, 인덱스 스캔 효율성을 높이는데에도 유리하다.
* 로컬 Prefixed 파티션 인덱스와 비교
* 글로벌 Prefixed 파티션 인덱스와 비교
* 비파티션 인덱스와 비교
* 일 단위 파티셔닝
(9) 액세스 효율을 고려한 인덱스 파티셔닝 선택 기준
* DW성 애플리케이션 환경
날짜 컬럼 기준으로 파티셔닝된 이력성 대용량 테이블이 많다.
따라서 관리적 측면뿐만 아니라 병렬 쿼리 활용 측면에서도 로컬 파티션 인덱스가 좋은 선택이다.
로컬 인덱스 중에서는 Nonprefixed 파티션 인덱스가 성능면에서 유리할 때가 많다.
* OLTP 성 애플리케이션 환경
비파티션 인덱스가 대개 좋은 선택이다.
(10) 인덱스 파티셔닝 제약을 고려한 데이터베이스 설계
* Unique 파티션 인덱스를 정의할 때는 인덱스 파티션 키가 모두 인덱스 구성 컬럼에 포함돼야 한다.
이 제약이 없다면 인덱스 키 값을 변경하거나 새로운 값을 입력할 때마다 중복 값 체크를 위해 매번 많은 인덱스 파티션을 탐색해야 하므로 DML성능이 저하된다. 따라서 당연히 필요한 제약이라고 할 수 있다.
* 글로벌 파티션 인덱스는 Prefixed 파티션이어야 한다.