6.3 파티션을 활용한 DML 튜닝
파티션을 활용하면 대량 추가/변경/삭제 작업을 빠르게 처리할 수 있다.
6.3.1 테이블 파티션
- 파티셔닝(Partitioning) : 테이블 또는 인덱스 데이터를 특정 컬럼(파티션 키) 값에 따라 별도 세그먼트에 나눠서 저장
- 파티션이 필요한 이유
- 관리적 측면 : 파티션 단위 백업, 추가, 삭제, 변경 -> 가용성 향상
- 성능적 측면 : 파티션 단위 조회 및 DML, 경합 또는 부하 분산
Range 파티션
- 가장 기초적인 방식으로 날짜 컬럼을 기준으로 파티셔닝
- 예) 주문일자 기준으로 분기별 Range 파티셔닝 가능
- 검색조건을 만족하는 파티션만 골라 읽어 이력성 데이터를 Full Scan 방식으로 조회 시 성능 크게 향상
- 보관주기 정책에 따라 과거데이터 파티션만 백업, 삭제하는 등 데이터 관리가 효율적이고 빠르다.
파티션 Pruning : Prune 은 불필요한 부분을 제거한다는 의미
파티션 테이블에 대한 SQL 성능 향상 원리는 파티션 Pruning 이다.(읽지 않아도 되는 파티션 섹그먼트는 액세스 대상에서 제회)
- Full Scan 하더라도 전체가 아닌 일부 파티션 세그먼트만 조회
- 파티션과 병렬처리가 만나면 효과가 증진
Hash 파티션
- 파티션 키 값을 해시 함수에 입력해서 반환받은 값이 같은 데이터를 세그먼트에 저장하는 방식
- 파티션 개수만 사용자가 결정, 데이터 분산 알고리즘은 오라클 내부 해시함수가 결정
- 해시 파티션은 고객ID 처럼 변별력이 좋고 데이터 분포가 고른 컬럼을 파티션 기준으로 선정해야 효과적이다.
- 해시 알고리즘 특성상 등치(=) 조건 또는 IN-List 조건으로 검색시 파티션 Pruniing 작동
리스트 파티션
- 사용자가 정의한 그룹핑 기준으로 데이터를 분할 저장하는 방식
- 순서가 아닌 불연속적인 값의 목록으로 파티션 결정
- 가능하면 각 파티션에 값이 고르게 분산되도록 해야 한다.
6.3.2 인덱스 파티션
테이블 파티션과 인덱스 파티션은 구분해야 한다.
- 비파티션 테이블(Non-Partitioned Table)
- 파티션 테이블(Partitioned Table)
파티션 인덱스는 각 파티션이 커버하는 테이블 파티션 범위에 따라 로컬과 글로벌로 나뉜다.
- 로컬 파티션 인덱스(Local Partitioned Index)
- 각 테이블 파시션과 인덱스 파티션이 서로 1:1 대응 -> 오라클리 자동으로 파티션 인덱스 관리
- 글로벌 파티션 인덱스(Global Partitioned Index)
- 로컬이 아닌경우 전부 글로벌이며 테이블 파티션과 인덱스 파티션이 독립적인 구성(파티션 키, 파티션 기준값 정의)을 갖는다.
- 비파티션 인덱스(Non-Partitioned Index)
로컬 파티션 인덱스
봄, 여름, 가을, 겨울에 옷장에 옷에 대한 색인이 있는것이다.
- 인덱스 파티션은 테이블 파티션 속성을 그대로 상속받는다.(테이블 파티션 키가 주문일자면 인덱스 파티션 키도 주문일자)
- 1:1 대응 관계로 오라클이 자동 관리
- 테이블 파티션 구성 변경(add, drop, exchange) 하더라도 인덱스를 재생성할 필요가 없어 서비스 중단없이 작업 가능
- 관리 편의성 매우 좋다
글로벌 파티션 인덱스
- 파티션을 테이블과 다르게 구성한 인덱스
- 테이블 파티션 구성을 변경(drop, exchange, split) 하는 순간 Unusable 상태로 바뀌므로 곧바로 인덱스 재생성해야 한다.
- 즉 그동안 서비스 중단이 발생한다.
비파티션 인덱스
- 인덱스를 파티셔닝하지 않은것이다.
- 글로벌 파티션 인덱스와 마찬가지로 테이블 파티션 구성 변경 시 서비스 중단한다.
Prefixed vs Nonprefixed
- Prefixed : 인덱스 파티션 키 컬럼이 인덱스 키 컬럼 왼쪽 선두에 위치한다.
- Nonprefixed : 인덱스 파티션 키 컬럼이 인덱스 키 컬럼 왼쪽 선두에 위치 하지 않는다. 파티션 키가 인덱스 컬럼에 아예 속하지 않을 때도 여기에 속한다.
- 로컬 Prefixed 파티션 인덱스
- 로컬 Nonprefixed 파티션 인덱스
- 글로벌 Prefixed 파티션 인덱스
- 비파티션 인덱스
중요한 인덱스 파티션 제약
"Unique 인덱스를 파티셔닝하려면 파티션 키가 모두 인덱스 구성 컬럼이어야 한다."
- 파티션 키와 PK 인덱스 키가 동일해야 한다.
- 다르다면 비효율성이 증가한다
- 예를 들어PK 인덱스 키 = 주문번호, 파티션 키 = 주문일자 인경우 주문번호 123 으로 주문 레코드 입력시 중복값이 있는지 확인하기 위해 인덱스 파티션을 모두 탐색해야 한다.(주문번호 123 레코드는 어떤 파티션에든 입력될 수 있기 때문)
- 추가로 그 레코드를 입력하고 커밋하기 전에 다른 트랜잭션에서 같은 번호로 다른 파티션 입력 현상을 막기 위해 Lock 메커니즘 추가로 적용
- Unique 인덱스를 파티셔닝할 때 파티션 키가 인덱스 컬럼에 포함되어야 한다는 조건은 DML 성능 보장을 위해 당연히 있어야 할 제약조건이다.
- 서비스 중단 없이 파티션 구조를 빠르게 변경하려면, PK 를 포함한 모든 인덱스가 로컬 파티션 인덱스이어야 한다.
- 가급적 인덱스를 로컬 파티션으로 구성해야 하고 그러기 위해 테이블 설계 시 PK 를 잘 구성해야 한다.
6.3.3 파티션을 활용한 대량 UPDATE 튜닝
- 입력/수정/삭제하는 데이터 비중이 5% 가 넘으면 인덱스를 활용하기 보다는 인덱스 없이 작업한 후 재성성하는게 더 빠르다.
- 예를 들어 10억건의 데이터가 있고 수정 비율이 5%가 넘으면, 데이터 수정시 인덱스를 Drop 하고 재성성하기 부담이 되 인덱스 를 그대로 두고 작업하는 경우가 많다 - 인덱스 전체 재생성의 부담성
- 테이블이 파티셔닝 되있고, 인덱스가 로컬 파티션인 경우 수정된 값을 갖는 임시 세그먼트를 만들어 원본 파티션과 바꿔치기하는 방식이 있다.
- 임시 테이블 생성
- 데이터를 읽어 임시 테이블에 입력하면서 데이터 수정
- 임시 테이블에 원본 테이블과 같은 구조로 인덱스 생성
4 .파티션과 임시 테이블 수정 - 임시 테이블 drop
6.3.4 파티션을 활용한 대량 DELETE 튜닝
삭제하면서 인덱스 관리는 시간 소요가 크다
왜 DELETE 가 느린가?
1. 테이블 레코드 삭제
2. 테이블 레코드 삭제에 대한 Undo Logging
3. 테이블 레코드 삭제에 대한 Redo Logging
4. 인덱스 레코드 삭제
5. 인덱스 레코드 삭제에 대한 Undo Logging
6. 인덱스 레코드 삭제에 대한 Redo Logging
7. Undo(2,5)에 대한 Redo Logging
대량 데이터를 지울 때는 남길 데이터만 백업했다가 재입력하는 방식이 빠르다
- 임시 테이블 생성, 남길 데이터만 복제
- 삭제 대상 테이블 파티션 Truncate
- 임시 테이블에 복제해둔 데이터를 원본 테이블에 입력
- 임시 테이블 drop
서비스 중단없이 파티션을 Drop 또는 Truncate 하기위한 조건
1, 파티션키와 커팅 기준 컬럼이 일치
2. 파티션 단위와 커팅 주기가 일치
3. 모든 인덱스가 로컬 파티션 인덱스
6.3.5 파티션을 활용한 대량 INSERT 튜닝
대량 데이터를 INSERT 하는 경우 인덱스를 Unusable 시켰다가 재성성하는 방식이 더 빠를 수 있다.
- 인덱스 Unusable 상태로 전환
- (가능하면 Direct Path Insert) 대량 데이터 입력
- 인덱스 재생성
파티션 테이블일 때
초대용량 인덱스 재생성 부담이 크므로, 인덱스를 그대로 둔(Unusable로 전환하지 않은) 상태로 INSERT 한다. 하지만 테이블 파티셔닝이 되있고, 인덱스도 로컬 파티션이라면 파티션 단위로 인덱스 재생성이 가능하므로 부담이 적다
- 작업 대상 테이블 파티션과 매칭되는 인덱스 파티션을 Unusable 상태로 전환
- (가능하면 Direct Path Insert) 대량 데이터 입력
- 인덱스 파티션 재생성
