And Brain said,

DB Partitioning, 거대한 데이터를 지탱하기 위한 DB의 슬래브 구조 본문

IT/Database

DB Partitioning, 거대한 데이터를 지탱하기 위한 DB의 슬래브 구조

The Man 2025. 3. 14. 15:40
반응형

개요

지금 현대 사회는 믿을 수 없을 정도로 데이터가 기하급수적으로 쏟아지고 있습니다. 대형 쇼핑몰에서는 초당 수백 건의 주문이 발생하고, 금융 시스템은 밀리초 단위로 수백만 개의 트랜잭션을 처리하며, 소셜 미디어는 매초마다 수많은 콘텐츠가 올라오고 있습니다. 하지만 이런 환경에서 단일 테이블에 데이터가 쌓이면 조회 속도가 느려지고, 백업과 삭제가 어려워지며, 시스템 부하가 치명적으로 증가합니다. 이럴 때 데이터를 논리적으로 나누어 부하를 분산시키고, 필요한 데이터만 빠르게 조회할 수 있도록 최적화하는 기술인 파티셔닝(Partitioning)은 여러분들에게 굉장히 유용한 도구가 되어줄 것 입니다.

 

1. Partitioning(파티셔닝)이란?

DB Partitioning(데이터베이스 파티셔닝)은 대량의 데이터를 논리적·물리적으로 여러 개의 작은 단위(파티션)로 나누어 저장하는 기술입니다. 이를 통해 쿼리 성능을 향상시키고, 데이터 관리를 용이하게 하며, 시스템의 확장성을 확보할 수 있게 됩니다.

1.1 Partitioning의 핵심 원리

DB Partitioning의 핵심 원리는 데이터를 특정 기준(날짜, 범위, 해시 등)에 따라 여러 개의 독립적인 저장 단위로 나누는 것인데, 아래와 같은 특징을 가집니다.

 

1. 각 파티션은 독립적인 스토리지 공간을 가지며, 개별적으로 쿼리 대상이 될 수 있음.

2. 데이터 조회 시 불필요한 파티션을 스캔하지 않도록 설계할 수 있어 성능이 향상됨.

 

1.2 Partitioning이 필요한 이유

성능 최적화 (Performance Optimization)

  • Full Table Scan을 방지하고 필요한 데이터만 조회할 수 있도록 최적화

  • 특정 파티션만 접근하여 쿼리 실행 시간을 단축

데이터 관리 용이성 (Manageability)

  • 파티션 단위로 백업, 복구, 삭제 가능 → 유지보수 부담 감소

  • 특정 파티션만 운영하거나 보관 가능 → 아카이빙 최적화

대용량 데이터 처리 최적화

  • OLTP(온라인 트랜잭션) 및 DW(데이터 웨어하우스) 환경에서 필수적인 기술

  • 트랜잭션 부하 분산 및 대량 데이터 연산 최적화 가능

확장성과 비용 절감 (Scalability & Cost Efficiency)

  • 새로운 데이터가 추가될 때 자동으로 새로운 파티션 생성 가능 (Interval Partitioning)

  • 테이블 전체를 변경하지 않고도 파티션 단위로 관리할 수 있어 비용 절감

1.3 Partitioning과 Sharding의 차이

비교 항목 Partitioning Sharding
적용 대상 단일 데이터베이스 내부 여러 개의 데이터베이스
데이터 분할 방식 논리적·물리적 테이블 분할 여러 개의 DB 인스턴스로 분산
관리 방식 한 DB 내에서 파티션을 나눔 분산된 DB 인프라에서 관리
주요 목적 성능 최적화, 관리 편의성 수평 확장, 고가용성

 

즉, Partitioning은 "한 개의 데이터베이스 내부에서" 데이터를 분할하는 개념이고, Sharding은 "여러 개의 데이터베이스로 분산"하는 개념입니다.

 

이 글에서는 Oracle Partitioning을 통해 Partitioning에 대해 더 자세히 알아볼 것입니다.

2. Oracle Partitioning의 유형

2.1 Range Partitioning (범위 기반)

데이터가 연속적인 값(날짜, 숫자 등)일 때 사용

 

먼저 sales 테이블을 만들고, 연도별로 데이터를 분할합니다.

CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    amount NUMBER
)
PARTITION BY RANGE (sale_date) (
    PARTITION sales_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
    PARTITION sales_2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))
);

 

이 테이블은 sale_date 기준으로 데이터를 2023년과 2024년으로 나눕니다.
sales_2023 → 2023년 데이터 저장
sales_2024 → 2024년 데이터 저장

 

이제 실제 데이터를 넣어서 파티션이 어떻게 분배되는지 확인해보면,

-- 2023년에 해당하는 데이터 (sales_2023 파티션으로 들어감)
INSERT INTO sales VALUES (1, TO_DATE('2023-06-15', 'YYYY-MM-DD'), 5000);

-- 2024년에 해당하는 데이터 (sales_2024 파티션으로 들어감)
INSERT INTO sales VALUES (2, TO_DATE('2024-03-10', 'YYYY-MM-DD'), 7000);
INSERT INTO sales VALUES (3, TO_DATE('2024-09-25', 'YYYY-MM-DD'), 6000);

-- 2025년 데이터 (해당하는 파티션이 없음 → 오류 발생)
INSERT INTO sales VALUES (4, TO_DATE('2025-02-05', 'YYYY-MM-DD'), 8000);

COMMIT;

 

 

2025년 데이터 삽입 시 오류 발생

 

오류: ORA-14400: INSERTED PARTITION KEY DOES NOT MAP TO ANY PARTITION
해결책: 2025년 데이터도 저장하려면 새로운 파티션을 추가해야 함

ALTER TABLE sales ADD PARTITION sales_2025 VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD'));

 

테이블이 제대로 파티셔닝 되었는지 확인하려면, DBA_TAB_PARTITIONS를 조회하면 됩니다.

SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE
FROM DBA_TAB_PARTITIONS
WHERE TABLE_NAME = 'SALES';

 

출력 예시:

TABLE_NAME | PARTITION_NAME | HIGH_VALUE  
-----------|---------------|------------
SALES      | SALES_2023    | TO_DATE(' 2024-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 
SALES      | SALES_2024    | TO_DATE(' 2025-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SALES      | SALES_2025    | TO_DATE(' 2026-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

 

HIGH_VALUE는 해당 파티션이 포함하는 최대 범위 값을 의미함.
2023년 데이터는 sales_2023에 저장, 2024년 데이터는 sales_2024에 저장

 

쿼리 최적화 Partition Pruning 적용 여부 확인

EXPLAIN PLAN FOR SELECT * FROM sales WHERE sale_date = TO_DATE('2024-06-01', 'YYYY-MM-DD');
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

 

* Partition Pruning? (파티션 가지치기)
Partition Pruning(파티션 프루닝)은 쿼리를 실행할 때 불필요한 파티션을 건너뛰고, 필요한 파티션만 검색하는 최적화 기법으로, 전체 테이블을 스캔하지 않고, 특정 파티션만 조회하는 것을 의미함.

필요한 데이터가 있는 파티션만 선택적으로 읽기 위함.

 

정상 적용된 경우 출력 예시: 

------------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |     1 |    35 |   274   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|       |     1 |    35 |   274   (0)| 00:00:01 |     2 |     2 |
|*  2 |   TABLE ACCESS FULL    | SALES |     1 |    35 |   274   (0)| 00:00:01 |     2 |     2 |
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("SALE_DATE"=TO_DATE(' 2024-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

 

PARTITION RANGE SINGLE → 특정 파티션 1개만 조회
Pstart = 2 | Pstop = 2 → sales_2024 파티션만 접근
Partition Pruning 성공 → 2024년 데이터만 검색됨

 

파티션 삭제

ALTER TABLE sales DROP PARTITION sales_2024;

2.2 List Partitioning (특정 값 기준)

List Partitioning은 데이터를 특정 리스트(List) 값(예: 국가, 지역, 제품 카테고리 등) 기준으로 나누어 저장하는 방법으로, 동일한 속성을 가진 데이터끼리 같은 파티션에 저장되어 조회 성능이 최적화됨.

 

 

이제 customers 테이블을 생성하고, 지역(region)을 기준으로 데이터를 분할해보면,

CREATE TABLE customers (
    customer_id NUMBER,
    region VARCHAR2(10)
)
PARTITION BY LIST (region) (
    PARTITION asia VALUES ('Korea', 'Japan', 'China'),
    PARTITION europe VALUES ('Germany', 'France', 'UK')
);

 

customers 테이블은 region 값을 기준으로 아시아(Asia), 유럽(Europe)으로 데이터를 분할합니다.
region 값이 'Korea', 'Japan', 'China' → asia 파티션에 저장됨
region 값이 'Germany', 'France', 'UK' → europe 파티션에 저장됨

 

-- 아시아 지역 고객 데이터 (asia 파티션으로 들어감)
INSERT INTO customers VALUES (1, 'Korea');
INSERT INTO customers VALUES (2, 'Japan');
INSERT INTO customers VALUES (3, 'China');

-- 유럽 지역 고객 데이터 (europe 파티션으로 들어감)
INSERT INTO customers VALUES (4, 'Germany');
INSERT INTO customers VALUES (5, 'France');
INSERT INTO customers VALUES (6, 'UK');

-- 미등록 지역 (해당하는 파티션이 없어서 오류 발생)
INSERT INTO customers VALUES (7, 'USA');

COMMIT;

 

'USA' 삽입 시 오류 발생
오류: ORA-14400: INSERTED PARTITION KEY DOES NOT MAP TO ANY PARTITION
해결책: 'USA' 데이터를 저장하려면 DEFAULT 파티션을 추가해야 함.

ALTER TABLE customers ADD PARTITION other_region VALUES (DEFAULT);

 

테이블이 제대로 파티셔닝 되었는지 확인하기 위해, DBA_TAB_PARTITIONS를 조회.

SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE
FROM DBA_TAB_PARTITIONS
WHERE TABLE_NAME = 'CUSTOMERS';

 

출력 예시:

TABLE_NAME | PARTITION_NAME | HIGH_VALUE  
-----------|---------------|------------
CUSTOMERS  | ASIA          | 'Korea', 'Japan', 'China'  
CUSTOMERS  | EUROPE        | 'Germany', 'France', 'UK'  
CUSTOMERS  | OTHER_REGION  | DEFAULT

 

쿼리 최적화 Partition Pruning 적용 여부 확인

EXPLAIN PLAN FOR
SELECT * FROM customers WHERE region = 'Korea';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

 

실행 결과 (Partition Pruning 적용 시)

------------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |     1 |    20 |   274   (0)| 00:00:01 |       |       |
|   1 |  PARTITION LIST SINGLE|       |     1 |    20 |   274   (0)| 00:00:01 |     KEY |     KEY |
|*  2 |   TABLE ACCESS FULL    | CUSTOMERS |     1 |    20 |   274   (0)| 00:00:01 |     1 |     1 |
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
      2 - filter("REGION"='Korea')

 

PARTITION LIST SINGLE → 특정 파티션(asia) 한 개만 검색
Pstart = 1 | Pstop = 1 → asia 파티션만 접근
Partition Pruning 적용됨 → 성능 최적화 성공

 

파티션 삭제

ALTER TABLE customers DROP PARTITION asia;

3. Partitioning 조회

3.1 현재 DB에서 Partitioning이 사용되고 있는지 확인

SELECT TABLE_NAME, PARTITIONING_TYPE, PARTITION_COUNT FROM DBA_PART_TABLES ORDER BY TABLE_NAME;

 

현재 어떤 테이블이 파티션을 사용하고 있는지 조회 가능

3.2 특정 테이블의 파티션 목록 확인

SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME = 'sales';

 

각 파티션이 어떤 기준으로 나누어져 있는지 확인 가능

4. 고급 Partitioning 유형

4.1 Hash Partitioning (부하 분산)

Hash Partitioning(해시 파티셔닝)은 특정 범위(Range)나 값(List)이 아닌, 해시(Hash) 함수를 사용하여 데이터를 균등하게 분배하는 기법으로, 데이터가 특정 값에 쏠리는 것을 방지하여 특정 파티션만 과부하 걸리는 문제를 해결합니다.
OLTP(온라인 트랜잭션) 환경에서 자주 사용

 

transactions(거래 내역) 테이블을 생성하고, trans_id 값을 기준으로 해시 함수를 이용하여 4개의 파티션에 분산해보면,

CREATE TABLE transactions (
    trans_id NUMBER,
    amount NUMBER
)
PARTITION BY HASH (trans_id)
PARTITIONS 4;

 

trans_id 값을 해싱하여 4개의 파티션 중 하나에 자동으로 저장되며, 특정 범위에 의존하지 않고 균등한 데이터 분배가 가능해집니다.

각 파티션에 데이터가 어떻게 분배되는지 직접 테스트해보면,

-- 샘플 데이터 삽입
INSERT INTO transactions VALUES (1, 100);
INSERT INTO transactions VALUES (2, 150);
INSERT INTO transactions VALUES (3, 200);
INSERT INTO transactions VALUES (4, 250);
INSERT INTO transactions VALUES (5, 300);
INSERT INTO transactions VALUES (6, 350);
INSERT INTO transactions VALUES (7, 400);
INSERT INTO transactions VALUES (8, 450);
INSERT INTO transactions VALUES (9, 500);
INSERT INTO transactions VALUES (10, 550);

COMMIT;

 

해싱된 trans_id 값이 자동으로 4개의 파티션 중 하나에 저장됨.

 

Hash Partitioning이 잘 적용되었는지 DBA_TAB_PARTITIONS을 조회하여 확인해보면,

SELECT table_name, partition_name, high_value 
FROM user_tab_partitions 
WHERE table_name = 'TRANSACTIONS';

 

출력 예시:

TABLE_NAME    | PARTITION_NAME | HIGH_VALUE  
-------------|---------------|------------  
TRANSACTIONS | SYS_P801      | [NULL]
TRANSACTIONS | SYS_P802      | [NULL] 
TRANSACTIONS | SYS_P803      | [NULL]
TRANSACTIONS | SYS_P804      | [NULL]

 

 

해시 파티셔닝에서는 HIGH_VALUE가 없음 → 범위 기반이 아니므로 특정 값 기준이 없다는 걸 확인하실 수 있습니다. 이는 Oracle이 자동으로 해시 함수를 적용하여 데이터를 배분하기 때문입니다.

 

어떤 데이터가 어떤 파티션에 저장되었는지 확인하려면 Oracle의 해시 함수(DBMS_UTILITY.GET_HASH_VALUE)를 사용하시면 됩니다.

SELECT trans_id, 
       DBMS_UTILITY.GET_HASH_VALUE(trans_id, 1, 4) AS partition_id 
FROM transactions;

 

출력 예시:

TRANS_ID  | PARTITION_ID
----------|-------------
1         | 2  
2         | 3  
3         | 1  
4         | 4  
5         | 2  
6         | 3  
7         | 1  
8         | 4  
9         | 2  
10        | 3

 

(* 만약 특정 해시 값에 집중되었다 하더라고 당황하지 마세요!

파티션 적용이 안되는 것이 아닌 샘플 데이터가 너무 적어서 특정 해시 값에 집중됐을 수도 있습니다.

데이터가 많아질수록 해시는 더 균등하게 분배될 것입니다.)

 

Partitioning이 잘 적용되었는지 EXPLAIN PLAN을 통해 확인해보면,

EXPLAIN PLAN FOR 
SELECT * FROM transactions WHERE trans_id = 7;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

 

실행 결과 (Partition Pruning 적용 시)

------------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |     16 |    416 |   274   (0)| 00:00:01 |       |       |
|   1 |  PARTITION HASH SINGLE|       |     16 |    416 |   274   (0)| 00:00:01 |     4 |     4 |
|*  2 |   TABLE ACCESS FULL    | TRANSACTIONS |     16 |    416 |   274   (0)| 00:00:01 |     4 |     4 |
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
         2 - filter("TRANS_ID"=7)

 

PARTITION HASH SINGLE → 특정 파티션만 검색
Pstart = 3 | Pstop = 3 → 3번 파티션만 조회 (Full Table Scan 방지)
Partition Pruning 적용됨 → 성능 최적화 성공

 

비교 항목 Hash Partitioning Range Partitioning
데이터 저장 방식 해시 함수로 랜덤 분배 특정 범위(Range)로 분할
쿼리 최적화 특정 ID 검색 시 유리 특정 날짜, 기간 검색 시 유리
부하 분산 균등한 데이터 분배 가능 특정 범위 데이터에 쏠릴 가능성 있음
OLTP/OLAP OLTP(트랜잭션 부하 분산에 적합) OLAP(데이터 분석 최적화에 적합)

 

OLTP(온라인 트랜잭션) 시스템에서 부하 분산이 필요하면 Hash Partitioning이 유리함
특정 값 검색이 많다면 Hash Partitioning, 날짜/범위 검색이 많다면 Range Partitioning을 사용

4.2 Composite Partitioning (복합 파티셔닝)

이제 파티셔닝의 진짜 위력을 체험할 차례입니다.
우리는 여태 Range(범위), List(목록), Hash(해시) 파티셔닝을 배웠지만, 현실의 데이터는 그렇게 단순하지 않습니다.
어떤 데이터는 시간 흐름에 따라 쌓이고, 또 어떤 데이터는 특정 그룹으로 나뉘며, 그 안에서도 균등한 부하 분산이 필요합니다.

그렇다면 이제 여러 개의 파티셔닝 기법을 조합하여, 데이터베이스의 성능을 최대로 끌어올릴 방법이 필요합니다.
그 비기가 바로 Composite Partitioning(복합 파티셔닝)입니다.


Range + Hash → 시간 순으로 데이터 정렬 + 균등 부하 분산
List + Hash → 특정 그룹별 데이터 구분 + 내부적으로 균등 분배
Range + List → 특정 기간 내에서 그룹별 저장

큰 데이터 그룹은 Range(범위) 또는 List(목록)로 나누고, 작은 데이터 그룹은 Hash(해시) 또는 다른 방식으로 다시 세분화할 수 있습니다.
OLTP(온라인 트랜잭션) & OLAP(데이터 분석) 환경 모두에서 활용 가능하며, 데이터 규모가 클수록 관리와 성능 최적화가 용이합니다.

 

4.2.1. Range + Hash (날짜별 + 지역별 균등 분배)

온라인 쇼핑몰에서 주문 데이터를 날짜별로 분류한 후, 같은 날짜 내에서는 지역별로 부하를 균등하게 분산해야 한다고 가정해보면,

1차 파티셔닝: 주문 날짜(order_date)를 기준으로 연도별 분류
2차 서브파티셔닝: 같은 연도 내에서는 지역(region)을 기준으로 해시 파티셔닝하여 부하를 고르게 분산

 

CREATE TABLE orders (
    order_id NUMBER,
    order_date DATE,
    region VARCHAR2(10)
)
PARTITION BY RANGE (order_date)  -- 1차 파티션: 연도별로 구분
SUBPARTITION BY HASH (region) -- 2차 파티션: 지역 해시 기반 분배
SUBPARTITIONS 4
(
    PARTITION orders_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
    (
        SUBPARTITION orders_2023_sub1,
        SUBPARTITION orders_2023_sub2,
        SUBPARTITION orders_2023_sub3,
        SUBPARTITION orders_2023_sub4
    ),
    PARTITION orders_2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))
    (
        SUBPARTITION orders_2024_sub1,
        SUBPARTITION orders_2024_sub2,
        SUBPARTITION orders_2024_sub3,
        SUBPARTITION orders_2024_sub4
    )
);

 

이제 데이터를 100개 이상 넣어보며 테스트를 해보면,

-- 2023년 데이터 (orders_2023 파티션)
INSERT INTO orders VALUES (1, TO_DATE('2023-03-15', 'YYYY-MM-DD'), 'Korea');
INSERT INTO orders VALUES (2, TO_DATE('2023-07-21', 'YYYY-MM-DD'), 'Japan');
INSERT INTO orders VALUES (3, TO_DATE('2023-11-10', 'YYYY-MM-DD'), 'USA');
INSERT INTO orders VALUES (4, TO_DATE('2023-05-05', 'YYYY-MM-DD'), 'UK');
INSERT INTO orders VALUES (5, TO_DATE('2023-08-18', 'YYYY-MM-DD'), 'France');
INSERT INTO orders VALUES (6, TO_DATE('2023-09-12', 'YYYY-MM-DD'), 'Germany');
INSERT INTO orders VALUES (7, TO_DATE('2023-01-25', 'YYYY-MM-DD'), 'China');
INSERT INTO orders VALUES (8, TO_DATE('2023-12-31', 'YYYY-MM-DD'), 'India');

-- 2024년 데이터 (orders_2024 파티션)
INSERT INTO orders VALUES (9, TO_DATE('2024-04-10', 'YYYY-MM-DD'), 'Korea');
INSERT INTO orders VALUES (10, TO_DATE('2024-06-18', 'YYYY-MM-DD'), 'Japan');
INSERT INTO orders VALUES (11, TO_DATE('2024-07-05', 'YYYY-MM-DD'), 'USA');
INSERT INTO orders VALUES (12, TO_DATE('2024-02-28', 'YYYY-MM-DD'), 'UK');
INSERT INTO orders VALUES (13, TO_DATE('2024-10-30', 'YYYY-MM-DD'), 'France');
INSERT INTO orders VALUES (14, TO_DATE('2024-11-15', 'YYYY-MM-DD'), 'Germany');
INSERT INTO orders VALUES (15, TO_DATE('2024-01-01', 'YYYY-MM-DD'), 'China');
INSERT INTO orders VALUES (16, TO_DATE('2024-12-25', 'YYYY-MM-DD'), 'India');
INSERT INTO orders VALUES (101, TO_DATE('2024-02-10', 'YYYY-MM-DD'), 'Korea');
INSERT INTO orders VALUES (102, TO_DATE('2024-03-12', 'YYYY-MM-DD'), 'Japan');
INSERT INTO orders VALUES (103, TO_DATE('2024-05-25', 'YYYY-MM-DD'), 'USA');
INSERT INTO orders VALUES (104, TO_DATE('2024-07-30', 'YYYY-MM-DD'), 'Germany');
INSERT INTO orders VALUES (105, TO_DATE('2024-08-15', 'YYYY-MM-DD'), 'France');
INSERT INTO orders VALUES (106, TO_DATE('2024-10-05', 'YYYY-MM-DD'), 'UK');
INSERT INTO orders VALUES (107, TO_DATE('2024-12-20', 'YYYY-MM-DD'), 'China');

-- 추가 데이터 대량 삽입 (100개 이상)
DECLARE
    i NUMBER;
    regions VARCHAR2(10);
BEGIN
    FOR i IN 17..120 LOOP
        IF MOD(i, 8) = 0 THEN regions := 'Korea';
        ELSIF MOD(i, 8) = 1 THEN regions := 'Japan';
        ELSIF MOD(i, 8) = 2 THEN regions := 'USA';
        ELSIF MOD(i, 8) = 3 THEN regions := 'UK';
        ELSIF MOD(i, 8) = 4 THEN regions := 'France';
        ELSIF MOD(i, 8) = 5 THEN regions := 'Germany';
        ELSIF MOD(i, 8) = 6 THEN regions := 'China';
        ELSE regions := 'India';
        END IF;
        
        INSERT INTO orders VALUES (i, TO_DATE('2023-01-01', 'YYYY-MM-DD') + MOD(i, 730), regions);
    END LOOP;
    COMMIT;
END;
/

 

아래 쿼리를 통해 데이터가 균등하게 분배됐는지 확인하실 수 있습니다.

SELECT sp.partition_name, sp.subpartition_name, COUNT(o.order_id) AS row_count
FROM user_tab_subpartitions sp
JOIN orders o ON sp.subpartition_name = 'ORDERS_' || EXTRACT(YEAR FROM o.order_date) || '_SUB' || (MOD(ORA_HASH(o.region), 4) + 1)
WHERE sp.table_name = 'ORDERS'
GROUP BY sp.partition_name, sp.subpartition_name
ORDER BY sp.partition_name, sp.subpartition_name;

 

마찬가지로, Partitioning이 잘 적용되었는지 EXPLAIN PLAN을 통해 확인해보면,

EXPLAIN PLAN FOR 
SELECT * FROM orders 
WHERE order_date = TO_DATE('2024-06-18', 'YYYY-MM-DD') 
AND region = 'Germany';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

 

실행 결과 (Partition Pruning 적용 시)

------------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |     1 |    29 |   274   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|       |     1 |    29 |   274   (0)| 00:00:01 |     2 |     2 |
|   2 |  PARTITION HASH SINGLE|       |     1 |    29 |   274   (0)| 00:00:01 |     2 |     2 |
|*  3 |   TABLE ACCESS FULL    | TRANSACTIONS |     1 |    29 |   274   (0)| 00:00:01 |     6 |    6 |
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
            3 - filter("ORDER_DATE"=TO_DATE(' 2024-06-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
                          "REGION"='Germany')

 

PARTITION RANGE SINGLE (연도 파티션 프루닝)
Pstart = 2, Pstop = 2
2024년(ORDERS_2024) 파티션만 선택됨

 

PARTITION HASH SINGLE (서브파티션 프루닝)
Pstart = 2, Pstop = 2
해시 서브파티션 중 특정 하나만 선택됨

 

TABLE ACCESS FULL (서브파티션 단위)
Pstart = 6, Pstop = 6
2024년 파티션 내에서 특정 서브파티션(ORDERS_2024_SUBX)만 조회

 

Partition Pruning 적용됨 → 성능 최적화 성공

 

4.2.2. Range + List (날짜별 + 그룹별 정리

이번에는 거래 데이터(Transactions) 를 연도별로 구분하고,
같은 연도 내에서도 VIP 고객과 일반 고객(Regular)으로 서브파티션을 나눠서 관리하는 방법을 상세하게 다뤄보도록 하겠습니다.

 

연도별로 데이터를 1차 파티션(Range Partitioning) 하고,
각 연도 내에서 VIP / Regular 고객을 2차 서브파티션(List Partitioning) 으로 나누면, 아래와 같은 생성 쿼리가 될 것입니다.
CREATE TABLE transactions (
    trans_id NUMBER,
    trans_date DATE,
    customer_type VARCHAR2(10)
)
PARTITION BY RANGE (trans_date) -- 1차 파티션: 연도별 분류
SUBPARTITION BY LIST (customer_type) -- 2차 파티션: 고객 유형별 분류
(
    PARTITION transactions_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
    (
        SUBPARTITION transactions_2023_vip VALUES ('VIP'),
        SUBPARTITION transactions_2023_regular VALUES ('Regular')
    ),

    PARTITION transactions_2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))
    (
        SUBPARTITION transactions_2024_vip VALUES ('VIP'),
        SUBPARTITION transactions_2024_regular VALUES ('Regular')
    )
);

 

예시 데이터도 넣어주고,

-- 2023년 VIP 고객
INSERT INTO transactions VALUES (9001, TO_DATE('2023-06-15', 'YYYY-MM-DD'), 'VIP');
INSERT INTO transactions VALUES (9005, TO_DATE('2023-02-10', 'YYYY-MM-DD'), 'VIP');
INSERT INTO transactions VALUES (9010, TO_DATE('2023-11-23', 'YYYY-MM-DD'), 'VIP');

-- 2023년 Regular 고객
INSERT INTO transactions VALUES (9002, TO_DATE('2023-12-20', 'YYYY-MM-DD'), 'Regular');
INSERT INTO transactions VALUES (9006, TO_DATE('2023-03-18', 'YYYY-MM-DD'), 'Regular');
INSERT INTO transactions VALUES (9011, TO_DATE('2023-07-29', 'YYYY-MM-DD'), 'Regular');

-- 2024년 VIP 고객
INSERT INTO transactions VALUES (9003, TO_DATE('2024-03-08', 'YYYY-MM-DD'), 'VIP');
INSERT INTO transactions VALUES (9007, TO_DATE('2024-05-12', 'YYYY-MM-DD'), 'VIP');
INSERT INTO transactions VALUES (9012, TO_DATE('2024-09-01', 'YYYY-MM-DD'), 'VIP');

-- 2024년 Regular 고객
INSERT INTO transactions VALUES (9004, TO_DATE('2024-07-11', 'YYYY-MM-DD'), 'Regular');
INSERT INTO transactions VALUES (9008, TO_DATE('2024-06-25', 'YYYY-MM-DD'), 'Regular');
INSERT INTO transactions VALUES (9013, TO_DATE('2024-10-10', 'YYYY-MM-DD'), 'Regular');

 

데이터가 잘 들어갔는지 아래의 쿼리로 확인하실 수 있습니다.

SELECT 
    p.partition_name, 
    sp.subpartition_name, 
    COUNT(t.trans_id) AS row_count
FROM 
    user_tab_subpartitions sp
JOIN 
    user_tab_partitions p 
    ON sp.table_name = p.table_name 
    AND sp.partition_name = p.partition_name
JOIN 
    transactions t 
    ON p.partition_name = 'TRANSACTIONS_' || EXTRACT(YEAR FROM t.trans_date)
    AND sp.subpartition_name = 
        CASE 
            WHEN t.customer_type = 'VIP' THEN 'TRANSACTIONS_' || EXTRACT(YEAR FROM t.trans_date) || '_VIP'
            WHEN t.customer_type = 'Regular' THEN 'TRANSACTIONS_' || EXTRACT(YEAR FROM t.trans_date) || '_REGULAR'
        END
WHERE 
    p.table_name = 'TRANSACTIONS'
GROUP BY 
    p.partition_name, sp.subpartition_name
ORDER BY 
    p.partition_name, sp.subpartition_name;

 

마지막으로, Partitioning이 잘 적용되었는지 EXPLAIN PLAN을 통해 확인해보면,
EXPLAIN PLAN FOR
SELECT * FROM transactions WHERE trans_date = TO_DATE('2024-07-11', 'YYYY-MM-DD') AND customer_type = 'Regular';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

 

실행 결과 (Partition Pruning 적용 시) 
------------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |     1 |    29 |   274   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|       |     1 |    29 |   274   (0)| 00:00:01 |     2 |     2 |
|   2 |  PARTITION LIST SINGLE|       |     1 |    29 |   274   (0)| 00:00:01 |     KEY |     KEY |
|*  3 |   TABLE ACCESS FULL    | TRANSACTIONS |     1 |    29 |   274   (0)| 00:00:01 |     4 |    4 |
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
               3 - filter("TRANS_DATE"=TO_DATE(' 2024-07-11 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

 

PARTITION RANGE SINGLE (연도 파티션 프루닝)
Pstart = 2, Pstop = 2
2024년(transactions_2024) 파티션만 선택됨

PARTITION LIST SINGLE → 2차 서브파티션(고객 등급별 VIP 또는 Regular)에 대한 프루닝 적용

TABLE ACCESS FULL | TRANSACTIONS | Pstart: 4, Pstop: 4 → 특정 서브파티션 한 개 만을 조회 (만약 여러 개의 서브파티션을 검색했다면 Pstart ~ Pstop 값이 넓게 나왔을 것)

Partition Pruning 적용됨 → 성능 최적화 성공

5. Partition Exchange – 실시간 데이터 이동

Partition Exchange는 기존 데이터를 삭제하지 않고, 새로운 테이블과 특정 파티션을 빠르게 교환하는 기술입니다.
이 기능을 활용하면 운영 환경에서 성능 저하 없이 대량 데이터를 이동할 수 있습니다.
대표적인 활용 사례로 ETL 처리, 아카이빙, 데이터 정리 및 배포 등이 있습니다.

 

5.1 Partition Exchange 실습

 

Orders 테이블 생성 (Range Partitioning 적용)
우선 주문 데이터를 연도별로 관리하는 테이블을 만들어줍니다.

CREATE TABLE orders (
    order_id NUMBER,
    order_date DATE,
    region VARCHAR2(10)
)
PARTITION BY RANGE (order_date)  -- 1차 파티션: 연도별로 구분
(
    PARTITION orders_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
    PARTITION orders_2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))
);

 

예시 데이터를 넣어주고,

INSERT INTO orders VALUES (1001, TO_DATE('2023-03-10', 'YYYY-MM-DD'), 'Korea');
INSERT INTO orders VALUES (1002, TO_DATE('2023-07-15', 'YYYY-MM-DD'), 'USA');
INSERT INTO orders VALUES (1003, TO_DATE('2023-12-05', 'YYYY-MM-DD'), 'Japan');
INSERT INTO orders VALUES (1004, TO_DATE('2024-02-20', 'YYYY-MM-DD'), 'Germany');
INSERT INTO orders VALUES (1005, TO_DATE('2024-06-30', 'YYYY-MM-DD'), 'France');
COMMIT;

 

이제 2023년 데이터(orders_2023 파티션)를 임시 테이블과 교환 후,

CREATE TABLE temp_orders AS SELECT * FROM orders WHERE 1=2;

ALTER TABLE orders EXCHANGE PARTITION orders_2023 WITH TABLE temp_orders;

 

조회해보면,

SELECT * FROM orders ORDER BY order_date;

SELECT * FROM temp_orders ORDER BY order_date;

 

orders 테이블의 결과 (orders_2023 데이터가 temp_orders로 이동됨)

ORDER_ID | ORDER_DATE | REGION
-------------------------------
1001     | 2023-03-10 | Korea
1002     | 2023-07-15 | USA
1003     | 2023-12-05 | Japan

 

5.2 Partition Exchange 활용법

(1) ETL 처리 속도 최적화
데이터를 staging_table에 먼저 적재 후, 검증이 끝나면 Partition Exchange로 운영 테이블에 교환
운영 환경에 직접 데이터 삽입 없이 안정적인 배포 가능


(2) 데이터 아카이빙
오래된 데이터는 temp_orders_archive 같은 테이블로 옮기고
기존 파티션을 비운 뒤 새로운 데이터만 유지 가능

 

만약 Partition Exchange 이후 다시 되돌리고 싶다면 아래 쿼리를 입력하시면 됩니다.

ALTER TABLE orders EXCHANGE PARTITION orders_2023 WITH TABLE temp_orders;

6.  Interval Partitioning – 자동 파티션 생성

6.1. Interval Partitioning 개념

Interval Partitioning(인터벌 파티셔닝)은 새로운 데이터가 삽입될 때 자동으로 새로운 파티션을 생성하는 기능입니다.
기존 Range Partitioning(범위 기반 파티셔닝)에서 우리는 2023년과 2024년 기준의 파티션을 만들어두고 2025년 데이터를 넣으려 했을때 에러를 마주했었습니다. 이처럼 범위 기반 파티셔닝은 미리 모든 파티션을 정의해야 한다는 점이 애로사항입니다.
하지만 Interval Partitioning을 사용하면 새로운 데이터가 특정 기준(예: 날짜, 월, 연도 등)에 맞춰 자동으로 파티션이 생성됩니다.

 

6.2. Interval Partitioning 실습

CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    amount NUMBER
)
PARTITION BY RANGE (sale_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))  -- 1개월 단위로 자동 생성
(
    PARTITION sales_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))  -- 초기 파티션만 설정
);

 

PARTITION BY RANGE (sale_date) → 날짜 기준으로 파티션을 나눔.
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) → 새로운 데이터가 기존 범위를 벗어나면, 1개월 단위로 새로운 파티션을 자동 생성
PARTITION sales_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')) → 초기 파티션(2023년 데이터)을 하나만 지정
(그 이후 데이터는 자동으로 파티션이 생성됨)

 

아직 새로운 데이터가 들어가지 않았으므로, 현재 존재하는 파티션 목록을 확인해보면,

SELECT table_name, partition_name, high_value 
FROM user_tab_partitions 
WHERE table_name = 'SALES';

 

TABLE_NAME | PARTITION_NAME | HIGH_VALUE
----------------------------------------
SALES      | SALES_2023     | TO_DATE(' 2024-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

 

현재 sales_2023 파티션(2024년 1월 이전 데이터 보관)만 존재하는 것을 확인하실 수 있습니다.

 

이제 다양한 날짜의 데이터를 삽입하면서 자동으로 새로운 파티션이 생성되는지 확인해보면,

-- 2023년 데이터 (기존 파티션에 들어감)
INSERT INTO sales VALUES (1, TO_DATE('2023-06-15', 'YYYY-MM-DD'), 500);
INSERT INTO sales VALUES (2, TO_DATE('2023-12-25', 'YYYY-MM-DD'), 700);

-- 2024년 데이터 (새로운 파티션 생성)
INSERT INTO sales VALUES (3, TO_DATE('2024-02-10', 'YYYY-MM-DD'), 800);
INSERT INTO sales VALUES (4, TO_DATE('2024-05-20', 'YYYY-MM-DD'), 600);
INSERT INTO sales VALUES (5, TO_DATE('2024-08-30', 'YYYY-MM-DD'), 900);

COMMIT;


2023-06-15 → 기존 sales_2023 파티션에 저장됨
2023-12-25 → 기존 sales_2023 파티션에 저장됨
2024-02-10 → 새로운 파티션(2024년 2월)이 자동 생성됨
2024-05-20 → 새로운 파티션(2024년 5월)이 자동 생성됨
2024-08-30 → 새로운 파티션(2024년 8월)이 자동 생성됨

다시 파티션 목록을 확인해보면,

SELECT table_name, partition_name, high_value 
FROM user_tab_partitions 
WHERE table_name = 'SALES';

 

TABLE_NAME | PARTITION_NAME | HIGH_VALUE
----------------------------------------
SALES      | SALES_2023     | TO_DATE(' 2024-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SALES      | SYS_P822       | TO_DATE(' 2024-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SALES      | SYS_P823       | TO_DATE(' 2024-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SALES      | SYS_P824       | TO_DATE(' 2024-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')


새로운 데이터가 들어오면서 새로운 파티션이 SYS_Pxxxx 형식으로 자동 생성된 것을 확인하실 수 있습니다.

 

Interval Partitioning은 보통 아래의 경우에 많이 활용됩니다.

 

대규모 트랜잭션 로그 저장소
쇼핑몰의 주문 기록 관리
IoT 센서 데이터 저장
사용자 활동 로그 관리 (웹 로그, 앱 로그 등)

7. 끝으로

단일 테이블에 수백만, 수천만, 나아가 억단위의 데이터가 쌓이면 조회 속도 저하, 백업 및 삭제 어려움, 시스템 부하 증가 같은 문제가 발생할 수밖에 없습니다.
이를 해결하기 위해 Partitioning(파티셔닝) 을 활용하면, 데이터 분할을 통해 부하를 분산하고 성능을 최적화할 수 있습니다.

 

우리는 이제 Range, List, Hash, Composite, Interval Partitioning 의 개념을 명확히 이해하고 실전 적용까지 완료했습니다.
여러분이 다루는 데이터베이스 환경에서도 Partitioning을 적극 활용하여 성능을 극대화하고, 유지보수를 효율적으로 수행해보시기 바랍니다.

 

감사합니다.

반응형

'IT > Database' 카테고리의 다른 글

PostgreSQL Custom type, 데이터 타입 공예술  (0) 2025.03.17
PostgreSQL, postgres 관리자 계정 주요 쿼리 정리  (0) 2025.03.14
AWR (Automatic Workload Repository), 오라클 DB의 진술문  (0) 2025.03.13
MSSQL, sa 관리자 계정 주요 쿼리 정리  (0) 2025.03.06
MSSQL, 주요 쿼리 정리  (0) 2025.03.05
'IT/Database' Related Articles more
Comments