친절한 SQL 튜닝_6장.DML 튜닝_3

 

DML 튜닝

 

6.3 파티션을 활용한 DML 튜닝

 

6.3.1 테이블 파티션

 

파티셔닝(Partitioning)은 테이블 또는 인덱스 데이터를 특정 컬럼(파티션 키) 값에 따라 별도 세그먼트에 나뉘서 저장하는 것을 말한다.

 

파티션이 필요한 이유를 관리적 측면과 성능적 측면으로 나뉘 짧게 요약하면 아래와 같다.

 

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

 

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

 

파티션에는 Range, 해시, 리스트 세 종류가 있다.

 

Range 파티션

 

오라클 8 버전부터 제공된 가장 기초적인 방식으로 주로 날자 컬럼을 기준으로 파티셔닝한다. 아래는 주문 테이블을 주문일자 기준으로 분기별 Range 파티셔닝하는 방법을 예시하고 있다.

 

CREATE TABLE 주문(
      주문번호 NUMBER
    , 주문일자 VARCHAR2(8)
    , 고객ID VARCHAR2(5)
    , 배송일자 VARCHAR2(8)
    , 주문금액 NUMBER
    , … 
)
PARTITION BY RANGE(주문일자) (
      PARTITION P2017_Q1 VALUES LESS THAN (‘20170401’)
    , PARTITION P2017_Q2 VALUES LESS THAN (‘20170701’)
    , PARTITION P2017_Q3 VALUES LESS THAN (‘20171001’)
    , PARTITION P2017_Q4 VALUES LESS THAN (‘20180101’)
    , PARTITION P2018_Q1 VALUES LESS THAN (‘20180401’)
    , PARTITION P9999_MX VALUES LESS THAN (MAXVALUE) -> 주문일자 >= ‘20180401’
);

 

위와 같은 파티션 테이블에 값을 입력하면 각 레코드를 파티션 키 값에 따라 분할 저장하고, 읽을 때도 검색 조건을 만족하는 파티션만 골라 읽을 수 있어 이력성 데이터를 Full Scan 방식으로 조회할 때 성능을 크게 향상한다. 보관주기 정책에 따라 과거 데이터가 저장된 파티션만 백업하고 삭제하는 등 데이터 관리 작업을 효율적이고 빠르게 수행할 수 있는 장점도 있다.

 

 

 

해시 파티션

 

해시 파티션은 Range 파티션에 이어 오라클 8i 버전부터 제공하기 시작했다. 파티션 키 값을 해시 함수에 입력해서 반환받은 값이 같은 데이터를 같은 세그먼트에 저장하는 방식이다. 파티션 개수만 사용자가 결정하고 데이터를 분산하는 알고리즘은 오라클 내부 해시함수가 결정한다.

 

해시 파티션은 고객ID처럼 변별력이 좋고 데이터 분포가 고른 컬럼을 파티션 기준으로 선정해야 효과적이다. 아래는 고객ID 기준으로 고객 테이블을 해시 파티셔닝하는 방법을 예시한다.

 

CREATE TABLE 고객(
      고객ID VARCHAR2(5)
    , 고객명 VARCHAR2(10)
    , … 
)
PARTITION BY HASH(고객ID) PARTITIONS 4;

 

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

 

 

 

리스트 파티션

 

오라클 9i 버전부터 제공하기 시작한 리스트 파티션은, 사용자가 정의한 그룹핑 기준에 따라 데이터를 분할 저장하는 방식이다. 아래는 지역분류 기준으로 인터넷매물 테이블을 리스트 파티셔닝하는 방법을 예시한다.

 

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 파티션에선 값의 순서에 따라 저장할 파티션이 결정되지만, 리스트 파티션에는 순서와 상관없이 불연속적인 값의 목록에 의해 결정된다.

 

해시 파티션과 비교하면, 해시 파티션은 오라클이 정한 해시 알고리즘에 따라 임의로 분할하는 반면, 리스트 파티션은 사용자가 정의한 논리적인 그룹에 따라 분할한다. 업무적인 친화도에 따라 그룹핑 기준을 정하되, 될 수 있으면 각 파티션에 값이 고르게 분산되도록 해야 한다.

 

 

 

6.3.2 인덱스 파티션

 

테이블 파티션과 인덱스 파티션은 구분돼야 한다. 인덱스 파티션은 테이블 파티션과 맞물려 다양한 구성이 존재한다. 다양한 인덱스 파티션 구성을 설명하기 위해 우선 테이블 파티션을 다음과 같이 구분하자.

 

- 비파티션 테이블(Non-Partitioned Table)

 

- 파티션 테이블(Partitioned Table)

 

인덱스도 테이블처럼 파티션 여부에 따라 파티션 인덱스와 비파티션 인덱스로 나위고, 파티션 인덱스는 각 파티션이 커버하는 테이블 파티션 범위에 따라 로컬과 글로벌로 나뉜다.

 

- 로컬 파티션 인덱스(Local Partitioned Index)

 

- 글로벌 파티션 인덱스(Global Partitioned Index)

 

- 비파티션 인덱스(Non-Partitioned Index)

 

로컬 파티션 인덱스는 각 테이블 파티션과 인덱스 파티션이 서로 1:1 대응 관계가 되도록 오라클이 자동으로 관리하는 파티션 인덱스를 말한다. 로컬이 아닌 파티션 인덱스는 ‘모두’ 글로벌 파티션 인덱스이며, 테이블 파티션과 독립적인 구성(파티션 키, 파티션 기준값 정의)을 갖는다.

 

 

 

로컬 파티션 인덱스

 

앞서 Range 파티션을 설명하면서 예시로 만든 주문 테이블에 로컬 파티션 인덱스를 만들어보자. 아래와 같이 CREATE INDEX 문 뒤에 ‘LOCAL’ 옵션을 추가하면 된다.

 

CREATE INDEX 주문_X01 ON 주문 (주문일자, 주문금액) LOCAL;
CREATE INDEX 주문_X02 ON 주문 (고객ID, 주문일자) LOCAL;

 

 

 

 

각 인덱스 파티션은 테이블 파티션 속성을 그대로 상속받는다. 따라서 테이블 파티션 키가 주문일자면 인덱스 파티션 키도 주문일자가 된다. 로컬 파티션 인덱스를 ‘로컬 인덱스’라고 줄여서 부르기도 한다.

 

 

 

로컬 파티션 인덱스는 테이블과 정확히 1:1 대응 관계를 갖도록 오라클이 파티션을 자동으로 관리해 준다. 테이블 파티션 구성을 변경(ADD, DROP, EXCHANGE 등)하더라도 인덱스를 재생성할 필요가 없다. 변경작업이 순식간에 끝나므로 피크(PEAK) 시간대만 피하면 서비스를 중단하지 않고도 작업할 수 있다. 로컬 파티션 인덱스의 장점은 이처럼 관리 편의성에 있다.

 

 

 

글로벌 파티션 인덱스

 

글로벌 파티션(Global Partitioned) 인덱스는 파티션을 테이블과 다르게 구성한 인덱스다. 구체적으로, 파티션 유형이 다르거나, 파티션 키가 다르거나, 파티션 기준값 정의가 다른 경우다. 비파티션 테이블이어도 인덱스는 파티셔닝할 수 있다.

 

앞서 Range 파티션을 설명하면서도 만든 주문 테이블에 『주문금액 + 주문일자』 순으로 글로벌파티션 인덱스를 만들어 보자. 아래와 같이 CREATE INDEX 문 뒤에 ‘GLOBAL’ 키워드 추가하고, 파티션을 정의하면 된다.

 

CREATE INDEX 주문_X03 ON 주문(주문금액, 주문일자) GLOBAL
PARTITION BY RANGE(주문금액) (
      PARTITION P_01 VALUES LESS THAN ( 100000 )
    , PARTITION P_MX VALUES LESS THAN ( MAXVALUE ) -> 주문금액 >= 100000
);

 

글로벌 파티션 인덱스는 테이블 파티션 구성을 변경(DROP, EXCHANGE, SPLIT 등)하는 순간 Unusable 상태로 바뀌므로 곧바로 인덱스를 재생성해 줘야 한다. 그동안 해당 테이블을 사용하는 서비스를 중단해야 한다.

 

테이블과 인덱스가 정확히 1:1 관계가 되도록 DB 관리자가 파티션을 직접 구성할 수도 있지만, 그렇다고 그것이 로컬 파티션은 아니다. 오라클이 인덱스 파티션을 자동으로 관리해 주지 않기 때문이다. 모양은 로컬 파티션이지만, 글로벌 파티션에 속한다.

 

 

 

비파티션 인덱스

 

비파티션(Non-Partitioned) 인덱스는 말 그대로 파티셔닝하지 않은 인덱스다. 만드는 방법은 아래와 같다. 일반 CREATE INDEX 문이다.

 

CREATE INDEX 주문_X04 ON 주문 (고객ID, 배송일자);

 

비파티션 인덱스는 여러 테이블 파티션을 가리킨다. 그런 의미에서 비파티션 인덱스를 ‘글로벌 비파티션 인덱스’라고 부르기도 한다.

 

비파티션 인덱스는 테이블 파티션 구성을 변경(DROP, EXCHANGE, SPLIT 등)하는 순간 Unusable 상태로 바뀌므로 곧바로 인덱스를 재생성해 줘야 한다. 그동안 해당 테이블을 사용하는 서비스를 중단해야 한다.

 

 

 

Prefixed vs. Nonprefixed

 

파티션 인데스를 Prefixed와 Nonprefixed로 나눌 수도 있다. 이는 인덱스 파티션 키 컬럼이 인덱스 구성상 왼쪽 선두 컬럼에 위치하는지에 따른 구분이다.

 

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

 

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

 

글로벌 파티션 인덱스는 Prefixed 파티션만 지원되므로 결과적으로 세 개의 파티션 인덱스가 있고, 비파티션 인덱스를 포함해 아래 네 가지 유형으로 최종 정리할 수 있다.

 

- 로컬 Prefixed 파티션 인덱스

 

- 로컬 Nonprefixed 파티션 인덱스

 

- 글로벌 Prefixed 파티션 인덱스

 

- 비파티션 인덱스

 

 

 

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

 

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

 

6.3.5 파티션을 활용한 대량 INSERT 튜닝

출처 : 친절한 SQL 튜닝 - 조시형 지음