DB 테이블 파티셔닝

DB 파티셔닝

DB에서 테이블 파티셔닝은 대량의 데이터를 나누어 저장하고 관리해 쿼리 성능을 향상시키는 기술 중 하나이다. 파티션은 큰 테이블을 관리 가능한 작은 부분(파티션)으로 나누고, 각 파티션은 하나의 독립적인 테이블처럼 동작한다. 하지만 사용자의 관점에서는 하나의 테이블로 보여 쉽게 사용할 수 있게 해준다.

테이블 파티셔닝의 장점은 다음과 같다.

1. 데이터 관리 용이성 : 특정 파티션만 백업하거나 복구하는 것이 가능하고, 자주 사용하지 않는 데이터를 다른 파티션으로 이동하거나 삭제할 수 있다.

2. 성능 향상 : SQL 쿼리가 파티션의 일부만 스캔하면 될 경우, 파티션 프루닝을 실행하여 불필요한 파티션을 제거해 성능을 향상시킬 수 있다.

3. 데이터 접근 시간 단축 : 파티션을 통해 필요한 데이터에 더 빠르게 접근할 수 있다. 예를들어, 특정 날짜 범위의 데이터만 필요한 쿼리가 있다면, 해당 범위가 있는 파티션만 조회하면 된다.

4. 데이터 분산 : 데이터를 여러 디스크에 분산시켜 저장 공간을 효율적으로 사용할 수 있다.

파티션 구조

오라클에서는 파티션을 세그먼트 단위로 구성한다. 파티션된 테이블은 하나의 세그먼트로 표현되며, 각 파티션은 해당 세그먼트 내에서 별도의 논리적인 저장 공간을 가지게 된다. 오라클에서 데이터 저장과 관리를 위한 논리적인 단위로는 블록, 익스텐드, 세그먼트 개념이 있다.

1. 블록 : 데이터베이스의 가장 작은 저장 단위로, 고정된 크기를 가지며, 일반적으로 8KB이다. 예를들어, 테이블에 데이터가 저장되는 경우, 데이터는 블록 단위로 저장되며, 하나의 블록에는 테이블의 한 부분이 저장된다.

2. 익스텐드 : 연속된 블록의 집합으로, 블록 그룹으로 구성되어 있으며, 데이터베이스에서 파일로 할당된다. 익스텐드는 파일 시스템의 디스크 공간을 물리적으로 할당하는 단위이다.

3. 세그먼트 : 하나 이상의 익스텐드로 구성된 논리적인 단위로, 테이블, 인덱스, 파티션 등과 같은 데이터베이스 개체를 저장한다.

오라클 구조

파티셔닝 유형

대표적인 파티셔닝 유형으로는 범위(Range), 리스트(List), 해시(Hash) 파티셔닝이 있다.

1. 범위 파티셔닝(Range Partition)

범위 파티셔닝은 지정된 범위의 값을 기준으로 데이터를 분할한다. 각 파티션은 연속된 값의 범위를 가지며, 각 범위는 겹치지 않는다. 일반적으로 시간 범위, 알파벳 범위와 같이 연속적인 값을 가진 데이터를 사용한다. 

'Sales'라는 테이블이 있고, 이 테이블에서 'sale_date' 컬럼 값에 따라 테이블을 범위파티션으로 나눠보자. 파티션을 월별로 설정하면, 이 'Sales' 테이블은 다음과 같이 세 개의 범위 파티션으로 나눌 수 있다.

Partition 1 : 'sale_date'가 2021년 1월인 모든 행을 포함한다.

Partition 2 : 'sale_date'가 2021년 2월인 모든 행을 포함한다.

Partition 3 : 'sale_date'가 2021년 3월인 모든 행을 포함한다.

CREATE TABLE Sales
(
    Sale_ID     NUMBER,
    Product     VARCHAR2(50),
    Sale_Date   DATE
)
PARTITION BY RANGE (Sale_Date)
(
    PARTITION sales_jan VALUES LESS THAN (TO_DATE('2021-02-01','YYYY-MM-DD')),
    PARTITION sales_feb VALUES LESS THAN (TO_DATE('2021-03-01','YYYY-MM-DD')),
    PARTITION sales_mar VALUES LESS THAN (TO_DATE('2021-04-01','YYYY-MM-DD'))
);

// 5월 데이터를 넣기 위해 파티션 추가 작업 필요
ALTER TABLE Sales ADD PARTITION sales_apr VALUES LESS THAN (TO_DATE('2021-05-01','YYYY-MM-DD'));

파티션을 사용할 때는 이렇게 주기적으로 파티션을 관리하고 추가해주는 작업이 필요한데, 이 작업을 자동화하기 위해서는 오라클에서는 'INTERVAL' 파티셔닝이라는 기능을 제공한다.

CREATE TABLE Sales
(
    Sale_ID     NUMBER,
    Product     VARCHAR2(50),
    Sale_Date   DATE
)
PARTITION BY RANGE (Sale_Date)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
    PARTITION sales_before_2021 VALUES LESS THAN (TO_DATE('2021-01-01','YYYY-MM-DD'))
);

위의 SQL 명령문은 'Sales' 테이블을 생성하고, 'sale_date' 컬럼을 기준으로 범위 파티션을 생성한다. 'INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))'는 매월 새로운 파티션을 자동으로 생성하도록 설정한다. 'sales_before_2021' 파티션은 2021년 1월1일 이전의 데이터를 담고 있으며 이후 'INTERVAL'에 의해 자동 생성되는 파티션은 'SYS_P0', 'SYS_P1' 등의 이름으로 지정된다. (데이터베이스 엔진이 자동으로 파티션을 관리 및 접근할 수 있도록 해줌)

지금까지 테이블을 모두 만들면서 파티션을 생성했는데, 오라클에서는 기존 테이블에 파티션을 추가하는 기능은 직접 제공하지는 않는다. 즉, 파티션을 적용하려면 처음부터 테이블을 파티션으로 생성하거나, 기존 테이블의 데이터를 새로운 파티션 테이블로 이동시키는 방법을 해야한다. 하지만 Oracle 12c부터는 'Online Table Redefinition' 기능을 제공해 테이블에 대한 파티션 적용 작업을 실시간으로 할 수 있게 해준다.

또 파티셔닝은 생성된 파티션을 삭제하는 내장 기능을 제공하지는 않는다. 따라서 DBA나 개발자가 데이터의 생명주기를 지정하고, 오래된 파티션을 주기적으로 삭제하는 등의 관리 작업을 진행해야 한다.

2. 리스트 파티셔닝

데이터를 특정 열의 값에 따라 파티션으로 분할하고, 각 파티션에는 해당 값과 일치하는 데이터가 저장된다. 

'region' 컬럼을 기준으로 리스트 파티셔닝을 구성해보자.

Partition 1 : '서울'에 해당하는 주문 데이터를 저장

Partition 2 : '경기'에 해당하는 주문 데이터를 저장

Partition 3 : '서울', '경기' 이외의 지역에 해당하는 주문 데이터를 저장

CREATE TABLE orders (
    order_id INT,
    customer VARCHAR(50),
    region VARCHAR(50),
    order_date DATE,
    amount DECIMAL(10, 2)
)
PARTITION BY LIST (region) (
    PARTITION seoul_partition VALUES ('서울'),
    PARTITION gyeonggi_partition VALUES ('경기'),
    PARTITION busan_partition VALUES ('부산'),
    PARTITION others_partition VALUES (DEFAULT)
);

3. 해시 파티셔닝

데이터를 해시 함수를 통해 정의된 파티션 수에 따라 분할하는 파티셔닝 방벙브로, 데이터를 고르게 분산시키고, 동일한 값을 가진 데이터는 동일한 파티션에 저장하여 데이터의 균형을 유지하는데 사용된다.

CREATE TABLE sales (
    sale_id INT,
    product VARCHAR(50),
    sale_date DATE,
    amount DECIMAL(10, 2)
)
PARTITION BY HASH (sale_id)
PARTITIONS 4;

위 쿼리를 사용하면 'sale_id' 열을 기준으로 파티셔닝하며, 데이터를 적재할 때, 'sale_id' 값에 따라 해시 함수를 통해 파티션을 결정하고 데이터를 해당 파티션에 저장한다. (위 예시에서는 1 ~ 4까지의 파티션 중 하나로 할당)

4. 컴포지트 파티셔닝

두 개 이상의 파티션 키를 결합하여 파티션을 구성하는 파티셔닝 방법으로, 여러 열을 조합하여 파티션 키로 사용한다. 예를들어, 지역과 날짜를 기준으로 데이터를 파티션으로 분할한다면, 각 지역 내에서도 날짜에 따라 파티션을 구성할 수 있다. 

CREATE TABLE sales (
    sale_id INT,
    region VARCHAR(50),
    sale_date DATE,
    amount DECIMAL(10, 2)
)
PARTITION BY RANGE (sale_date)
SUBPARTITION BY LIST (region)
SUBPARTITION TEMPLATE (
    SUBPARTITION usa VALUES ('USA'),
    SUBPARTITION europe VALUES ('Europe'),
    SUBPARTITION asia VALUES ('Asia'),
    SUBPARTITION others VALUES (DEFAULT)
)
(
    PARTITION p1 VALUES LESS THAN (TO_DATE('2022-01-01', 'YYYY-MM-DD')),
    PARTITION p2 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD'))
);

위의 쿼리는 'sales' 테이블을 'sale_date'를 기준으로 RANGE 파티셔닝을 수행하고, 'region'을 기준으로 LIST 서브파티셔닝을 수행한다.

인덱스 관리

파티셔닝된 테이블에서 인덱스는 두 가지 유형이 있다. 

1. 로컬 인덱스 : 각 파티션에 대해 개별적으로 생성되는 인덱스로(각 파티션에 대해 별도의 인덱스 세그먼트를 생성), 각 파티션을 독립적으로 관리할 수 있으며 한 파티션에 대한 작업이 다른 파티션의 인덱스에 영향을 미치지 않는다.

2. 글로벌 인덱스 : 테이블 전체를 대상으로 하나의 인덱스를 생성한다. 이는 파티션 간의 데이터를 효과적으로 조회할 수 있도록 해준다. 

파티션 프루닝

쿼리 실행 중 파티션 테이블에서 불필요한 파티션을 제거하여 쿼리 성능을 향상시키는 기술로, 쿼리의 조건 절과 파티션 테이블의 구조를 이용하여 적절한 파티션만 액세스하도록 최적화하는 과정이다.

SELECT *
FROM sales
WHERE sale_date >= DATE '2022-01-01'
  AND sale_date < DATE '2022-02-01';

sales 테이블에서 'sale_date'가 범위 파티션으로 되어 있을 경우, 데이터베이스 엔진은 이 쿼리를 실행할 때 조건 절에 있는 파티션 키와 관련된 조건을 파악하고, 해당 조건에 맞는 파티션만 액세스한다. 따라서 조건에 해당하는 파티션만 스캔하여 데이터를 조회하도록 할 수 있다.