[SQL 튜닝] 파티션을 활용한 DML튜닝

테이블 파티션

- 파티셔닝은 테이블 또는 인덱스 데이터를 특정 컬럼(파티션 키) 값에 따라 별도 세크먼트에 나눠서 저장하는 것

- 파티션이 필요한 이유

1. 관리적측면 : 파티션 단위 백업, 추가, 삭제, 변경 --> 가용성 향상 

2. 성능 측면 : 파티션 단위 조회 및 DML, 경합 또는 부하 분산.

1) Range 파티션

• 주로 날짜 컬럼을 기준으로 파티셔닝함.
• 파티션 테이블에 대한 SQL 성능 향상 원리는 파티션 Pruning(=elimination)에 있음.
• 파티션 pruning이란 SQL 하드파싱이나 실행 시점에 조건절을 분석해서, 읽지 않아도 되는 파티션 세그먼트를 액세스 대상에서 제외하는 기능.
• 파티션도 클러스터, IOT와 마찬가지로 관련있는 데이터가 물리적으로 인접하도록 저장하는 클러스터링 기술에 속함.
• 클러스터는 데이터를 블록단위로 모아 저장하지만, 파티셔닝은 세그먼트 단위로 모아서 저장

2) 해시파티션 (오라클 8i 이후)

• 파티션 키 값을 해시 함수에 입력해서 반환 받은 값이 같은 데이터를 같은 세그먼트에 저장하는 방식. 
• 사용자는 파티션의 개수만 결정하고, 오라클 내부 해시함수가 데이터 분산 함. 
• 해시 파티션의 파티션 기준 선정은 고객ID처럼 변별력이 좋고 데이터 분포가 고른 컬럼을 선정해야 효과적. 
• 조건절 비교값에 똑같은 해시 함수를 적용함으로써 읽을 파티션을 결정. 
• 해시 알고리즘 특성상 등치 조건(=) 또는 IN-List 조건으로 검색할 때만 파티션 Pruning 작동

3) 리스트 파티션 (9i이후)

• 사용자가 정의한 그룹핑 기준에 따라 데이터를 분할 저장하는 방식

 

인덱스 파티션

- 테이블 파티션과 인덱스 파티션은 구분되어야 함. 

1) 로컬 파티션 인덱스

• 각 테이블 파티션과 인덱스 파티션이 1:1 대응 관계가 되도록 오라클이 자동으로 관리.(계절별로 별도 색인을 만드는 것과 같음)
• 피크 시간대가 아니면 서비스 중단없이 작업 가능 

2) 글로벌 파티션 인덱스

• 파티션이 테이블과 다르게 구성된 인덱스. 파티션 유형이 다르거나, 파티션 키가 다르거나, 파티션 기준값 정의가 다른경우. 
• 비파티션 테이블이어도 인덱스는 파티셔닝 가능
• 글로벌 파티션 인덱스는 테이블 파티션 구성을 변경(DROP, EXCHANGE, SPLIT 등)하는 순간 UNUSABLE상태로 변경되므로 바로 인덱스 재생성이 필요하며 그동안 해당 테이블을 사용하는 서비스 중단해야 함. 
• 모양은 로컬파티션이지만 클로벌 파티션에 속함

3) 비파티션 인덱스(글로벌 비파티션 인덱스)

• 파티셔닝하지 않은 인덱스
• 비파티션 인덱스는 여러 테이블 파티션을 가리키므로 비파티션 인덱스를 ‘글로벌 비파티션 인덱스’라고 부르기도 함
• 비파티션 인덱스는 테이블 파티션 구성을 변경하는 순간 Unusable상태로 변경되므로, 곧바로 인덱스 재생성 필요. 그동안 해당 테이블을 사용하는 서비스 중단

 

Prefixed VS. Nonprefixed 

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

Prefixed : 인덱스 파티션 키 컬럼이 인덱스 키 컬럼 왼쪽 선두에 위치한다.

Nonprefixed : 인덱스 파티션 키 컬럼이 인덱스 키 컬럼 왼쪽 선두에 위치하지 않는다. 파티션 키가 인덱스 컬럼에 아예 속하지 않을 때도 여기에 속한다.

 

인덱스 파티션 제약

• Unique 인덱스를 파티셔닝하려면 파티션 키가 모두 인덱스 구성 컬럼이어야 함.
• 서비스 중단 없이 파티션 구조를 빠르게 변경하려면 PK를 포함한 모든 인덱스가 로컬 파티션 인덱스이어야 함.

 

파티션을 활용한 대량 UPDATE 튜닝

• 입력/수정.삭제 하는 데이터 비중이 5%가 넘는다면 인덱스를 그대로 둔 상태에서 작업하기 보다는 인덱스 없이 작업한 후에 재생성하는 것이 나음
• 테이블이 파티셔닝되어있고 인덱스도 다행이 로컬 파티션이라면, 수정된 값을 갖는 임시 세그먼트를 만들어 원본 파티션과 바꿔치기하면 된다.

 

파티션을 활용한 대량 DELETE 튜닝

• 파티션 Drop를 이용한 대량 데이터 삭제
• 파티션 Truncate를 이용한 대량 데이터 삭제
• 서비스 중단없이 파티션을 Drop, Truncate를 하려면 아래 조건을 모두 만족해야함

1. 파티션 키와 커팅 기준 칼럼이 일치해야함
2. 파티션 단위와 커팅 주기가 일치해야함
3. 모든 인덱스가 로컬 파티션 인덱스이어야 함

 

파티션을 활용한 대량 Insert 튜닝

• 비파티션 테이블일 때 손익분기점을 넘는 대량 테이블을 Insert하려면, 인덱스를 Unusable 시켰다가 재생성하는 방식이 더 빠를 수 있음
• 파티션 테이블을 때, 초대용량 인덱스를 재생성하는 부담이 크기 때문에, 시간이 더 걸리더라도 왠만하면 인덱스를 그대로 둔(Unusable로 전환하지 않은) 상태로 Insert함