파티션 활용 튜닝

• 파티션 이용시 대량 추가/변경/삭제작업을 빠르게 처리할 수 있다.
• 이를 이해하기위해서는 파티션에대한 많은 사전지식이 필요

1. 테이블 파티션

• 파티셔닝(Partitioning) : 테이블 또는 인덱스 데이터를 특정컬럼(파티션 키) 값에 따라 별도 세그먼트에 나눠저장하는것
• 계절별로 옷을관리하면 외출할때 옷을 쉽고 빠르게 찾을수있다.
• 데이터도 월별, 분기별, 반기별, 연별로 분할해서 저장해 두면 빠르게 조회할수 있고 관리도 용이
• 일반적으로 시계열에 따라 Range방식으로 분할하지만, 그 외 다른기준(리스트 or 해시방식)으로도 분할가능
• 파티션이 필요한 이유
  • 관리적 측면 : 파티션 단위 백업, 추가, 삭제, 변경 > 가용성향상
  • 성능적 측면 : 파티션 단위 조회 및 DML, 경합 또는 부하 분산
• 파티션종류는 Range, 해시, 리스트 세종류가있다

Range 파티션

• 오라클 8버전부터 제공된 갖아 기초적인 방식, 주로날짜컬럼 기준으로 파티셔닝
• 각 레코드를 파티션 키 값에 따라 분할 저장하고, 읽을 때도 검색 조건을 만족하는 파티션만 골라 읽을 수 있어 이력성 데이터 Full Scan방식으로 조회할 때 성능을 크게 향상
• 보관주기 정책에 따라 과거 데이터가 저장된 파티션만 백업하고 삭제하는 등 데이터 관리 작업을 효율적이고 빠르게 수행할 수 잇는 장점
• 파티션 테이블에 대한 SQL성능 향상 원리는 파티션Pruning(Ellimination)
• 파티션 Pruning은 SQL하드파싱이나 실행 시점에 조건절을 분석해서 읽지 않아도되는 파티션 세그먼트를 액세스 대상에서 제외하는 기능
• 인덱스를 사용하기에는 FULL SCAN보다 느리고, FULL SCAN하자니 사이즈가 너무큰경우(100GB이상)
  테이블을 100만건단위로 나눠서 저장하면, Full Scan하더라도 전체가아닌 일부 파티션 세그먼트만 읽고 멈출수있어 성능을 크게 향상
• 파티션과 병렬 처리가 만나면 그 효과는 배가 된다.
• 파티션 테이블도 인덱스로 액세스할 수 있지만, 파티션 Pruning을 이용한 테이블 스캔보다 훨씬 느리다.
• 파티션도 클러스터 IOT와 마찬가지로 관련있는 데이터가 흩어지지않고 물리적으로 인접하도록 저장하는 클러스터링 기술에 속한다.
• 클러스터와다른점은 세그먼트 단위로 모아서 저장하는것
• 클러스터는 데이터를 블록단위로 모아 저장, IOT는 데이터를 정렬된 순서로 저장하는 구조

해시 파티션

• 해시 파티션은 Range파티션에 이어 오라클 8i버전부터 제공하기 시작
• 파티션 키 값을 해시함수에 입력해서 반환받은 값이 같은 데이터를 같은 세그먼트에 저장하는 방식
• 파티션 개수만 사용자가 결정하고 데이터 분산알고리즘은 오라클 내부 해시함수가 결정
• 해시파티션은 고객ID처럼 변별력이 좋고 데이터분포가 고른 컬럼을 파티션 기준으로 선정해야 효과적
• 고객ID기준 고객 테이블을 해시 파티셩하는 방법

  create table 고객 (고객 ID varchar2(5) 고객명 varchar2(10), ''')
  partition by ash(고객ID) partitions 4;

• 검색시 조건절 비교값(상수 or 변수)에 똑같은 해시함수를 적용하여 읽을 파티션 결정
• 해시 알고리즘 특성상 등치조건 또는 IN-List조건으로 검색할때만 파티션 Pruning작동

리스트 파티션

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

   create table 인터넷매물 (물건코드 varchar2(5), 지역분류 varchar2(4), ''')
      partition by list(지역분류)(
   	partition P_지역1 values('서울')
     ,partition P_지역2 values('경기','인천)
     ,partition P_지역3 values('부산','대구','대전','광주')
     ,partition P_기타 values(DEFAULT) --기타지역
   )   

• Range파티션에선 값의 순서에따라 저장파티션 결정하지만, 리스트파티션에서는 순서상관없이 불연속적 값의 목록에 의해 결정
• 해시파티션은 오라클이 정한 해시 알고리즘에 따라 임의로 분할, 리스트파티션은 사용자가 정의한 논리적그룹에 따라 분할
• 업무적인 친화도에따라 그룹핑 기준을 정하되, 될수 있으면 각 파티션에 값이 고르게 분산되도록 해야 한다.

2. 인덱스 파티션

• 테이블 파티션과 인덱스파티션은 구분돼야한다
• 인덱스 파티션은 테이블 파티션과 맞물려 다양한 구성존재

로컬 파티션 인덱스

• 각 테이블 파티션과 인덱스 파티션이 서로 1:1대응관계가되도록 오라클이 자동으로관리하는 파티션 인덱스
• 계절별 옷관리 색인을 만드는것과같다.
• 관리 편의성 - 테이블 파티션 구성을 변경하더라도 인덱스 재생성 불필요, 변경작업이 순식간에끝나 피크시간대만 피하면 서비스 중단없이 작업가능

글로벌 파티션 인덱스

• 파티션을 테이블과 다르게 구성한 인덱스
• 파티션 유형이 다르거나, 파티션 키가 다르거나, 파티션 기준값정의가 다른경우
• 비파티션 테이블이어도 인덱스는 파티셔닝 할 수 있다.
• create index문뒤에 'GLOBAL' 키워드를추가하고 파티션을 정의하면 된다.
• 글로벌 파티션 인덱스는 테이블 파티션 구성변경하는순간 Unusuable상태로 바뀌므로 곧바로 인덱스를 재생성 해줘야 한다, 그동안 테이블을 사용하는 서비스를 중단해야 한다.

비파티션 인덱스

• 파티셔닝 하지않은 인덱스, 일반 인덱스와 생성방법이 똑같음.
• 여러 테이블 파티션을 가리킨다, 글로벌 비파티션 인덱스라고 부르기도함.
• 테이블 파티션 구성변경시 Unusuable상태로 바뀜, 곧바로 인덱스 재생성해줘야한다, 해당테이블사용서비스도 중단해야한다

Prefixed vs. Nonprefixed

• 파티션인덱스를 Prefixed와 Nonprefixed로 나눌수도있다
• 이는 인덱스 파티션 키 컬럼이 인덱스 구성상 왼쪽 선두컬럼에 위치하는지에 따른 구분
  • Prefixed : 인덱스 파티션 키 컬럼이 인덱스 키 컬럼 왼쪽 선두에 위치
  • Nonprefixed : 인덱스 파티션 키 컬럼이 인덱스 키 컬럼 왼쪽선두에 위치하지 않는다.
    파티션 키가 인덱스 컬럼에 아예 속하지 않을때도 여기 속한다.

중요한 인덱스 파티션 제약

- Unique인덱스를 파티셔닝하려면, 파티션키가 모두 인덱스 구성 컬럼이어야 한다.

3. 파티셔닝활용한 대량 UPDATE 튜닝

• 인덱스가 DML성능에 큰 영향을 미치므로 대량데이터 수정시에는 인덱스를 DROP하거나 Unusuable상태로 변경하는작업을 많이 활용
• 손익분기점은 5%정도로 결정됨(테이블이 클스록 손익분기점은 더 낮아진다.)
• 입력/수정/삭제 데이터 비중이 5%를 넘으면 인덱스 없이 작업한후에 재생성하는게 더 빠르다

4. 파티셔닝 활용한 대량 DELETE튜닝

• 수천만건 데이터를 삭제할 때도, 인덱스를 실시간으로 관리하려면 많은 시간이 소요된다.
• 초대용량 테이블 인덱스를 모두 DROP했다가 다시 생성하기도 어려운일
• UPDATE는 변경대상 컬럼을 퐇마하는 인덱스만 재생성하면되지만 DELETE는 모든 인덱스를 재생성해야한다.

  파티션 DROP을 이용한 대량 데이터 삭제

  파티션 Truncate를 이용한 대량 데이터 삭제

• 테이블에 남길 데이터가 대다수이면 대량데이터를 지울게 아니라 남길 데이터만 백업했다가 재입력하는방식이 빠르다

5. 파티션활용 대량 INSERT 튜닝

5.1 비파티션 테이블일때

• 비파티션 테이블에 손익분기점을 넘는 대량 데이터를 INSERT하려면, 인덱스를 Unusable시켰다 재생성박식이 더빠를 수 있다
• nologging모드 > Unusable전환 > 대량데이터 입력(가능하면Direct Path Insert) > Index재생성(가능하면 nologging) > loggin모드로 전환(nologging모드 였다면)

  5.2 파티션 테이블일때

• 초 대용량 인덱스를 재생성하는 부담이 만만치않아 시간이 더오래걸리더라도 실무에선 웬만하면 인덱스를 그대로 둔(Unusable전환하지않은) 상태로 INSERT
• BUT, 테이블이 파티셔닝 돼 있고, 인덱스도 다행히 로컬 파티션이라면 괜찮음, 파티션단위로 인덱스 재생성이 가능
• 작업순서
  • 작업대상 테이블 파티션을 nologgin모드로전환(가능하면)
  • 작업대상 테이블 파티션과 매칭되는 인덱스파티션 Unusable상태로전환
  • 대량데이터 입력(가능하면 Direct Path Insert방식으로)
  • 인덱스파티션 재생성(가능하면 nologging모드)
  • 작업파티션을 loggin모드로 전환(nologging모드로 작업했다면)