분할¶

분할 키¶

분할 키는 정의된 분할들에 데이터를 분배하기 위해 분할 방식에서 사용하는 표현식이다. 분할 키로 사용할 수 있는 칼럼의 데이터 타입은 다음과 같다.

• CHAR
• VARCHAR
• SMALLINT
• INT
• BIGINT
• DATE
• TIME
• TIMESTAMP
• DATETIME

분할 키에는 다음과 같은 제약 사항이 적용된다.

• 분할 키는 분할 테이블에서 하나의 칼럼만을 사용해야 한다.
• 집계 함수 및 분석 함수, 논리 연산자, 비교 연산자는 분할 키 표현식에 사용할 수 없다.
• 다음 함수 및 표현식은 분할 키 표현식에서 허용되지 않는다.
  • CASE
  • CHARSET()
  • CHR()
  • COALESCE()
  • SERIAL_CURRENT_VALUE()
  • SERIAL_NEXT_VALUE()
  • DECODE()
  • DECR()
  • INCR()
  • DRAND()
  • DRANDOM()
  • GREATEST()
  • LEAST()
  • IF()
  • IFNULL()
  • INSTR()
  • NVL()
  • NVL2()
  • ROWNUM
  • INST_NUM()
  • USER
  • PRIOR
  • WIDTH_BUCKET()

영역 분할¶

영역 분할(range partitioning)은 각 분할에 대해 지정된 값의 영역으로 테이블을 분할하는 방법이다. 범위는 겹치지 않는 연속된 구간으로 정의된다. 이 분할 방법은 테이블의 데이터가 영역 구간으로 나누어질 수 있을 때 가장 유용한 방법이다. 예를 들면, 주문 정보 테이블에서 주문 날짜 또는 사용자 테이블에서 나이 영역으로 분할하는 경우이다. 영역 분할은 거의 모든 검색 조건이 영역을 매칭하는데 사용될 수 있기 때문에 분할 프루닝 측면에서 가장 다양하게 활용되는 분할 기법이다.

테이블은 CREATE 또는 ALTER 문에서 PARTITION BY RANGE 절을 사용하여 분할될 수 있다.

CREATE TABLE table_name (
   ...
)
PARTITION BY RANGE ( <partitioning_key> ) (
    PARTITION partition_name VALUES LESS THAN ( <range_value> ),
    PARTITION partition_name VALUES LESS THAN ( <range_value> ),
    ...
)

ALTER TABLE table_name
PARTITION BY RANGE ( <partitioning_key> ) (
    PARTITION partition_name VALUES LESS THAN ( <range_value> ),
    PARTITION partition_name VALUES LESS THAN ( <range_value> ),
    ...
)

• partitioning_key: 분할 키를 지정한다.
• partition_name: 분할 이름을 지정한다.
• range_value: 분할 키의 최대 값을 지정한다. range_value 보다 작은 분할 키 값을 가지는 레코드들은 모두 해당 분할에 저장된다.

다음은 올림픽 참가국 정보를 담은 participant2 테이블을 참가한 올림픽의 개최연도를 기준으로 2000년도 이전 참가국(before_2000 분할)과 2008년도 이전 참가국(before_2008 분할)로 나누는 영역 분할을 생성하는 예제이다.

CREATE TABLE participant2 (
    host_year INT,
    nation CHAR(3),
    gold INT,
    silver INT,
    bronze INT
)
PARTITION BY RANGE (host_year) (
    PARTITION before_2000 VALUES LESS THAN (2000),
    PARTITION before_2008 VALUES LESS THAN (2008)
);

분할을 생성할 때, 사용자가 제공한 영역을 가장 작은 값부터 가장 큰 값까지 정렬하고 정렬된 리스트에서 겹치지 않는 간격을 생성한다. 위 예에서 생성된 영역의 간격은 [-inf, 2000)와 [2000, 2008)이다. 분할에 대한 무제한의 최대값을 지정하고 싶으면 MAXVALUE 식별자를 사용한다.

ALTER TABLE participant2 ADD PARTITION (
  PARTITION before_2012 VALUES LESS THAN (2012),
  PARTITION last_one VALUES LESS THAN MAXVALUE
);

투플을 영역 분할 테이블에 삽입할 때, 시스템은 분할 키를 평가하여 해당 투플이 어느 분할 영역에 속하게 될 것인가를 식별한다. 분할 키 값이 NULL이면, 해당 투플은 가장 작은 영역의 분할에 저장된다. 분할 키 값에 해당하는 영역이 없으면 오류를 반환한다. 또한 투플을 업데이트할 때도 새로운 분할 키 값에 해당하는 영역이 존재하지 않으면 오류를 반환한다.

해시 분할¶

해시 분할은 지정된 개수의 분할로 데이터를 분배하기 위해 사용되는 분할 기법이다. 이 분할 기법은 테이블 데이터의 영역이나 리스트가 의미 없는 값을 포함할 때 유용하다. 예를 들어, 키워드 테이블이나 user_id가 가장 관심 있는 값인 사용자 테이블과 같은 경우에 해당된다. 분할 키 값이 테이블 데이터를 고르게 분배한다면, 해시 분할 기법은 정의된 분할들에 테이블 데이터를 고르게 배분해준다. 해시 분할에 대한 분할 프루닝 최적화는 동등 조건(=과 IN 조건)에만 적용될 수 있는데, 대부분의 질의가 분할 키에 대한 동등 조건으로 주어질 때에 해시 분할이 유용하다.

CREATE 또는 ALTER 문에서 PARTITION BY HASH 절을 사용하여 해시 분할을 할 수 있다.

CREATE TABLE table_name (
   ...
)
PARTITION BY HASH ( <partitioning_key> )
PARTITIONS ( number_of_partitions )

ALTER TABLE table_name
PARTITION BY HASH (<partitioning_key>)
PARTITIONS (number_of_partitions)

• partitioning_key: 분할 키를 지정한다.
• number_of_partitions: 생성할 분할의 개수를 지정한다.

다음은 국가 코드와 국가 이름의 정보를 담은 nation2 테이블을 생성하고 code 값을 기준으로 4개의 해시 분할을 정의하는 예제이다. 해시 분할은 분할의 개수만 지정하고 이름은 지정하지 않는다.

CREATE TABLE nation2 (
  code CHAR (3),
  name VARCHAR (50)
)
PARTITION BY HASH (code) PARTITIONS 4;

해시 분할 테이블에 삽입될 때 데이터를 저장할 분할은 분할 키의 해시 값에 의해 결정된다. 분할 키 값이 NULL이면, 해당 레코드는 첫번째 분할에 저장된다.

리스트 분할¶

리스트 분할은 사용자가 지정한 분할 키 값의 리스트에 따라 테이블을 분할하는 기법이다. 분할을 위한 값의 리스트는 겹치는 값이 없어야 한다. 이 분할 기법은 사원 테이블의 부서 ID, 사용자 테이블의 국가 코드와 같은 경우처럼 테이블 데이터가 의미 있는 값의 리스트로 나누어질 때 유용하다. 해시 분할과 마찬가지로, 리스트 분할에 대한 분할 프루닝 최적화는 동등 조건(=과 IN 조건)에만 적용된다.

CREATE 또는 ALTER 문에서 PARTITION BY LIST 절을 사용하여 리스트 분할을 할 수 있다.

CREATE TABLE table_name (
  ...
)
PARTITION BY LIST ( <partitioning_key> ) (
  PARTITION partition_name VALUES IN ( <values_list> ),
  PARTITION partition_name VALUES IN ( <values_list> ),
  ...
)

ALTER TABLE table_name
PARTITION BY LIST ( <partitioning_key> ) (
  PARTITION partition_name VALUES IN ( <values_list> ),
  PARTITION partition_name VALUES IN ( <values_list> ),
  ...
)

• partitioning_key : 분할 키를 지정한다.
• partition_name : 분할 명을 지정한다.
• partition_value_list : 분할의 기준이 되는 값의 목록을 지정한다.

다음은 선수의 이름과 종목 정보를 담고 있는 athlete2 테이블을 생성하고 종목에 따른 리스트 분할을 정의하는 예제이다.

CREATE TABLE athlete2 (name VARCHAR (40), event VARCHAR (30))
PARTITION BY LIST (event) (
    PARTITION event1 VALUES IN ('Swimming', 'Athletics'),
    PARTITION event2 VALUES IN ('Judo', 'Taekwondo', 'Boxing'),
    PARTITION event3 VALUES IN ('Football', 'Basketball', 'Baseball')
);

리스트 분할 테이블에 투플을 삽입할 때 분할 키 값은 분할에 정의된 리스트 값 중 하나에 속해야 한다. 리스트 분할의 경우 분할 키 값이 NULL일 때 자동으로 특정 분할을 할당하지 않고 오류가 발생된다. NULL 값을 저장하려면 다음의 예와 같이 NULL을 포함하는 분할을 생성해야 한다.

CREATE TABLE athlete2 (name VARCHAR (40), event VARCHAR (30))
PARTITION BY LIST (event) (
    PARTITION event1 VALUES IN ('Swimming', 'Athletics' ),
    PARTITION event2 VALUES IN ('Judo', 'Taekwondo', 'Boxing'),
    PARTITION event3 VALUES IN ('Football', 'Basketball', 'Baseball', NULL)
);

분할 프루닝¶

분할 프루닝(partition pruning)은 검색 조건을 통해 데이터 검색 범위를 한정시키는 최적화 기법이다. 분할 프루닝을 수행하는 과정 중에 분할 정의를 고려하여 질의문에 대해 항상 거짓인 분할들을 식별한다. 다음 예의 SELECT 문에 대해 before_2008과 before_2012 분할을 제외한 나머지 분할들은 모두 YEAR (opening_date)가 2004 보다 작다는 것을 알 수 있기 때문에, before_2008과 before_2012 분할에 대해서만 질의가 이루어진다.

CREATE TABLE olympic2 (opening_date DATE, host_nation VARCHAR (40))
PARTITION BY RANGE (YEAR(opening_date)) (
    PARTITION before_1996 VALUES LESS THAN (1996),
    PARTITION before_2000 VALUES LESS THAN (2000),
    PARTITION before_2004 VALUES LESS THAN (2004),
    PARTITION before_2008 VALUES LESS THAN (2008),
    PARTITION before_2012 VALUES LESS THAN (2012)
);

SELECT opening_date, host_nation
FROM olympic2
WHERE YEAR(opening_date) > 2004;

분할 프루닝은 디스크 I/O와 질의 수행 중 처리해야 할 데이터 양을 크게 줄여준다. 프루닝의 이점을 최대한 활용하기 위해서 프루닝이 수행되는 시점을 이해하는 것이 중요하다. 분할을 프루닝하려면 다음 조건들을 만족해야 한다.

• 분할 키는 WHERE 절에서 다른 표현식을 통하지 않고 직접 사용되어야 한다.
• 영역 분할에서 분할 키는 범위 조건(<, >, BETWEEN 등)이나 동등 조건(=, IN 등)으로 사용되어야 한다.
• 리스트 분할과 해시 분할에서 분할 키는 동등 조건(=, IN 등)으로 사용되어야 한다.

다음 예는 위의 olympic2 테이블을 가지고 프루닝이 어떻게 수행되는가를 설명한다.

-- prune all partitions except before_2012
SELECT host_nation
FROM olympic2
WHERE YEAR (opening_date) >= 2008;

-- prune all partitions except before_2008
SELECT host_nation
FROM olympic2
WHERE YEAR(opening_date) BETWEEN 2005 and 2007;

-- no partition is pruned because partitioning key is not used
SELECT host_nation
FROM olympic2
WHERE opening_date = '2008-01-02';

-- no partition is pruned because partitioning key is not used directly
SELECT host_nation
FROM olympic2
WHERE YEAR(opening_date) + 1 = 2008;

-- no partition is pruned because there is no useful predicate in the WHERE clause
SELECT host_nation
FROM olympic2
WHERE YEAR(opening_date) != 2008;

사용자는 분할 테이블을 접근하는 방법 외에 시스템에 의해 부여된 분할 이름을 직접 명시하거나 table PARTITION (name) 절을 사용하여 각 분할에 직접 접근할 수 있다.

-- to specify a partition with its table name
SELECT * FROM olympic2__p__before_2008;

-- to specify a partition with PARTITION clause
SELECT * FROM olympic2 PARTITION (before_2008);

위의 before_2008 분할에 접근하는 두 개의 질의는 분할(partition)이 아닌 일반 테이블인 것처럼 보인다. 분할 테이블(partitioned table)에서는 사용할 수 없는 최적화 기법(이에 대한 자세한 내용은 분할 시 참고 사항 참고)을 이 방법을 통해서 사용할 수 있기 때문에 매우 유용하게 활용될 수 있다. 사용자가 분할을 직접 명시하면 해당 질의는 지정한 분할에만 제한된다는 것을 유의해야 한다. 질의의 WHERE 절 조건을 만족하는 레코드를 포함하더라도 명시되지 않은 분할들은 질의 수행 시에 전혀 고려되지 않으며, INSERT와 UPDATE 문에 의해 삽입/수정되는 레코드가 지정된 분할에 속하지 않는 경우 오류가 발생된다.

분할 테이블(partitioned table)이 아닌 각 분할(partition)에 대해 질의를 수행하면, 분할 기법의 몇 가지 이점을 잃게 된다. 예를 들어, 사용자가 단지 분할 테이블에 대해서만 질의를 수행하면 사용자의 응용 프로그램을 수정할 필요 없이 추후에 해당 테이블을 재분할하거나 특정 분할을 제거(drop)할 수 있다. 사용자가 분할에 직접 접근하면 이러한 이점을 잃게 된다. 또한, INSERT 문에서 특정 분할을 명시하는 것이 허용되기는 하지만 이로 인해 얻을 수 있는 성능 이득이 없으므로 권장되지 않는다.

분할 관리¶

ALTER 문의 분할 지정 절을 사용하여 다음과 같이 분할 테이블을 관리할 수 있다.

1. 분할 테이블을 일반 테이블로 변경
2. 분할 재구성
3. 이미 존재하는 분할 테이블에 분할 추가
4. 분할 제거하기
5. 분할을 일반 테이블로 승격

ALTER {TABLE | CLASS} table_name REMOVE PARTITIONING

ALTER {TABLE | CLASS} table_name
REORGANIZE PARTITION <alter_partition_name_comma_list>
INTO ( <partition_definition_comma_list> )

partition_definition_comma_list ::=
PARTITION partition_name VALUES LESS THAN ( <range_value> ), ...

ALTER TABLE participant2
REORGANIZE PARTITION before_2000 INTO (
  PARTITION before_1996 VALUES LESS THAN (1996),
  PARTITION before_2000 VALUES LESS THAN (2000)
);

ALTER TABLE participant2
REORGANIZE PARTITION before_1996, before_2000 INTO (
  PARTITION before_2000 VALUES LESS THAN (2000)
);

ALTER TABLE athlete2
REORGANIZE PARTITION event2 INTO (
    PARTITION event2_1 VALUES IN ('Judo'),
    PARTITION event2_2 VALUES IN ('Taekwondo', 'Boxing')
);

ALTER TABLE athlete2
REORGANIZE PARTITION event2_1, event2_2 INTO (
    PARTITION event2 VALUES IN ('Judo', 'Taekwondo', 'Boxing')
);

ALTER {TABLE | CLASS} table_name
ADD PARTITION (<partition_definitions_comma_list>)

ALTER TABLE participant2 ADD PARTITION (
  PARTITION before_2012 VALUES LESS THAN (2012),
  PARTITION last_one VALUES LESS THAN MAXVALUE
);

ALTER {TABLE | CLASS} table_name
DROP PARTITION partition_name_list

ALTER TABLE participant2 DROP PARTITION before_2000;

ALTER {TABLE | CLASS} table_name
COALESCE PARTITION number_of_shrinking_partitions

ALTER TABLE nation2 COALESCE PARTITION 1;

ALTER {TABLE | CLASS} table_name
ADD PARTITION PARTITIONS number

ALTER TABLE nation2 ADD PARTITION PARTITIONS 3;

ALTER TABLE table_name PROMOTE PARTITION <partition_name_list>

CREATE TABLE t (i INT) PARTITION BY LIST (i) (
    PARTITION p0 VALUES IN (1, 2),
    PARTITION p1 VALUES IN (3, 4),
    PARTITION p2 VALUES IN (5, 6)
);

INSERT INTO t VALUES(1), (2), (3), (4), (5), (6);

csql> ;schema t
=== <Help: Schema of a Class> ===
...
 <Partitions>
     PARTITION BY LIST ([i])
     PARTITION p0 VALUES IN (1, 2)
     PARTITION p1 VALUES IN (3, 4)
     PARTITION p2 VALUES IN (5, 6)

csql> SELECT * FROM t;

=== <Result of SELECT Command in Line 1> ===
            i
=============
            1
            2
            3
            4
            5
            6

ALTER TABLE t PROMOTE PARTITION p0, p2;

csql> ;schema t
=== <Help: Schema of a Class> ===
 <Class Name>
     t
 ...
 <Partitions>
     PARTITION BY LIST ([i])
     PARTITION p1 VALUES IN (3, 4)

csql> SELECT * FROM t;

=== <Result of SELECT Command in Line 1> ===
            i
=============
            3
            4

분할 테이블의 인덱스¶

분할 테이블에서 생성되는 인덱스는 로컬 인덱스 또는 글로벌 인덱스로 구분된다. 글로벌 인덱스는 모든 분할들의 데이터를 포함하는 하나의 인덱스를 유지하지만, 로컬 인덱스는 각 분할마다 독자적으로 하나의 인덱스를 유지한다. 분할 테이블에 인덱스를 생성할 때, 로컬 인덱스가 될 것인지 혹은 글로벌 인덱스가 될 것인지는 다음 규칙에 따라 시스템이 결정한다.

• 모든 기본 키는 글로벌 인덱스이다.
• 모든 외래 키는 로컬 인덱스이다.
• 모든 비고유 인덱스는 로컬 인덱스이다.
• 고유 인덱스는 분할 키가 고유 인덱스에 속하면 로컬 인덱스이고, 그렇지 않으면 글로벌 인덱스이다.

다음 예는 시스템이 로컬 인덱스와 글로벌 인덱스를 결정하는 방법이다.

CREATE TABLE t(i INTEGER, j INTEGER k INTEGER)
PARTITION BY HASH(i) PARTITIONS 5;

-- pk_t_i is global because it is a primary key
ALTER TABLE t ADD CONSTRAINT pk_t_i PRIMARY KEY(i);

-- i_t_j and i_t_j_k are local indexes
CREATE INDEX i_t_j ON t(j);
CREATE INDEX i_t_j_k ON t(j, k);

-- u_t_i_j is a local index because the partitioning key (i) is part of the index definition
CREATE UNIQUE INDEX u_t_i_j ON t(i, j);

-- u_t_j_k is a global index because the partitioning key (i) is not part of the index definition
CREATE UNIQUE INDEX u_t_j_k ON t(j, k);

가능한 한 최대로 로컬 인덱스를 정의하는 것이 성능상 중요하다. 시스템은 글로벌 인덱스를 사용하여 여러 개의 분할을 함께 스캔할 수 있도록 인덱스 스캔을 최적화하지는 않는다. 글로벌 인덱스 스캔 시에 프루닝되지 않은 각 분할에 대해 별개의 인덱스 스캔이 수행된다. 글로벌 인덱스를 통해 각 분할을 접근하는 과정에서 다른 분할에 속한 데이터 또한 디스크에서 읽게 되기 때문에 로컬 인덱스 스캔보다 좋지 못한 성능을 보인다. INSERT 문도 또한 로컬 인덱스가 더 작기 때문에 로컬 인덱스에서 더 나은 성능을 보인다.

분할 시 참고 사항¶

분할 테이블은 일반적으로 일반 테이블처럼 동작한다. 그러나 테이블을 분할하여 얻는 이점을 충분히 활용하기 위해 고려해야 할 몇 가지 유의 사항이 있다.

CREATE TABLE t (c CHAR(50) COLLATE utf8_bin)
PARTITION BY LIST (c) (
    PARTITION p0 VALUES IN (_utf8'x'),
    PARTITION p1 VALUES IN (_iso88591'y')
);

ERROR: Invalid codeset '_iso88591' for partition value. Expecting '_utf8' codeset.

CREATE TABLE tbl (str STRING) COLLATE utf8_en_ci
PARTITION BY LIST (str) (
    PARTITION p0 VALUES IN ('test'),
    PARTITION p1 VALUES IN ('TEST')
);

ERROR: Partition definition is duplicated. 'p1'