ORACLE 파티셔닝

파티셔닝이란 ? 

테이블 또는 인덱스 데이터를 파티션 단위로 나누어 저장하는 것을 의미한다. 논리적으로 하나의 테이블을 물리적으로 나누어 저장한다. ex) 로그 테이블

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

-> 파티셔닝은 내부에 몇 개의 세그먼트를 생성하고 그것들이 논리적으로 하나의 오브젝트임을 메타 정보로 딕셔너리에 저장해두는 것에 그치지 않는다. 파티션 되지 않은 일반 테이블은 테이블과 세그먼트가 1:1관계지만, 파티션 테이블일 때는 1:M 관계다. 인덱스를 파티션할 때도 마찬가지다. 

• 관리적 측면에서 파티션 단위로 백업, 추가, 삭제, 변경 가능해 이점이 있다. ex) 보관주기가 지난 데이터를 별도 장치에 백업하고 지우는 일은 데이터베이스 관리자들의 일상적인 작업인데 이를 파티션의 도움으로 쉽게 해결할 수 있다. 
• 성능적 측면에서 파티션 단위 조회 및 DML 수행, 경합 및 부하분산 등의 이점이 있다. ex) 데이터를 빠르게 검색할 필요가 있을 때 인덱스를 이용하는 방법과 테이블 전체를 스캔하는 방법 크게 두가지가 있다. 그러나, 인덱스를 이용해 건건이 테이블을 액세스하는 방식은 일정량을 넘는 순간 오히려 테이블 full scan 보다 성능상 이점이 없다. 그렇다고  full scan하면 절대량이 많아 부담스러운 경우가 있는데 이를 해결하기 위해  full scan을 하더라도 일부 파티션 세그만트만 읽고 멈추게 할 수 있다. 
• 테이블 파티셔닝 여부를 결졍할 때는 데이터가 얼마나 고르게 분산될 수 있느냐가 가장 중요한 관심사항이다. 

파티셔닝 기본구조

1. 수동 파티셔닝 :

파티션 뷰를 통해 파티션 기능을 구현, 파티션 뷰의 핵심은 뷰 쿼리에 사용된 조건절에 부합하는 테이블만 읽는다. 이를 파티션  Pruning 이라고 한다. 

2. 파티션 테이블 : 

partition by 절로 파티션 뷰의 Base 테이블에 체크제약을 설정하는 것과 같은 역할을 한다. 

 

->  근본적으로 쿼리 조건절에 부합하는 세그먼트만 찾아 읽는 기능(파티션 Purning)은 과거(수동 파티셔닝)나 지금(파티션 테이블)이나 옵티마이저의 몫이다. 

 

테이블 파티션 유형

1. Range 파티셔닝

 파티션 키 값의 범위로 분할, 주로 날짜 칼럼을 기준으로 함( 판매 데이터를 월별로 분할), 파티션 키로는 하나 이상의 칼럼을 지정할 수 있고 최대 16개까지 허용된다. 

DBA 실수로 신규 파티션 생성을 하지 않으면 월초 또는 연초에 데이터가 입력되지 않을 수도 있는데 maxvalue 파티션을 반드시 생성해두는 것이 좋다. 참고로 11g부터는 Range 파티션을 생성할 때 interval 기준을 정의함으로써 정해진 간격으로 파티션이 자동으로 추가되게 할 수 있다. 

 

2. Hash 파티셔닝

 파티션 키 값에 해시 함수를 적용하고 함수를 적용한 결과값이 같은 레코드를 같은 파티션 세그먼트에 저장하는 방식이다. 파티션 키의 데이터 분포가 고른 칼럼이어야 효과적이다. 따라서 고객 id처럼 변별력이 좋고 데이터 분포가 고른 칼럼을 파티션 기준 칼럼으로 선정해야 효과적이다. 

해시파티셔닝은 파티션 개수만 사용자가 결정하고 데이터를 분산시키는 해싱 알고리즘은 오라클에 의해 결정된다. 따라서 파티션 키를 잘못 선정하면 데이터가 고르게 분산되지 않아 파티션의 이점이 사라질 수도 있다. 데이터가 모든 파티션에 고르게 분산되도록 DBMS가 관리해 각 로우의 저장위치 예측 불가능함.

 

3. List 파티셔닝

 불연속적인 값의 목록을 각 파티션에 지정 순서와 상관없이 사용자가 미리 정한 기준에 따라 데이터 분할 저장한다.( 판매 데이터를 지역별로 분할) 리스트파티셔닝은 Range, Hash 파티셔닝과 달리 단일 칼럼으로만 파티션 키를 지정할 수 있다. 리스트 파티션에 default 파티션을 생성해두어야 안전하다. 이유는 Range 파티션에 maxvalue 파티션을 반드시 생성해야하는 경우와 같다. 

 

4. Composite 파티셔닝

결합 파티셔닝을 구성하면 서브 파티션마다 세그먼트를 하나씩 할당하고, 서브파티션 단위로 데이터를 저장한다. 즉, 주 파티션 키에 따라 1차적으로 데이터를 분배하고, 서브 파티션 키에 따라 최종적으로 저장할 위치(세그먼트)를 결정한다.

 1. Range + Hash 결합 파티셔닝 : ex) 주문일자 기준으로 주문테이블을 분기별 Range 파티셔닝하고, 그 안에 다시 고객 id 를 기준으로 해시 파티셔닝하는 것

2. Range + 리스트 결합 파티셔닝 : ex)판매일자 기준으로 분기별 Range 파티셔닝 하고, 그안에 다시 판매점 기준으로 리스트 파티셔닝 하는 것

3. 기타 결합 파티셔닝 :  Range-Range, 리스트-해시, 리스트-리스트, 리스트-Range

파티션 Pruning

옵티마이저가 SQL의 대상 테이블과 조건절을 분석해 불필요한 파티션을 액세스 대상에서 제외해 조회나 DML수행시 성능개선 가능하게 함. 실행시점에  SQL 조건절을 분석하여 읽지 않아도 되는 파티션 세그먼트를 액세스 대상에서 제외시키는 기능. 

1. 기본 파티션 Pruning

1) 정적 파티션 Pruning

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

2) 동적 파티션 Pruning

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

 

 

인덱스 파티셔닝

1. Local 파티션 인덱스

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

 

2. Global 파티션 인덱스

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

 

3. Prefixed 파티션 인덱스

파티션 인덱스를 생성할 때, 파티션 키 칼럼을 인덱스 키 칼럼 왼쪽 선두에 두는 것

 

4. Nonprefixed 파티션 인덱스 

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

 

-> 경우의 수 2*2 로 4가지임.

 

* 옵티마이저 : 가장 효율적인 방법으로 SQL을 수행할 최적의 처리 경로를 생성해주는 DBMS의 핵심엔진

https://coding-factory.tistory.com/743

[DB] 데이터베이스 옵티마이저(Optimizer)에 대하여

https://www.youtube.com/watch?v=P7LqaEO-nGU