Oracle - 파티셔닝 활용

2025-01-06

2026-04-25

Oracle 파티셔닝 활용

오라클 파티셔닝을 활용하여 대용량 테이블의 성능과 관리 효율성을 향상시키는 방법을 다룹니다.

파티셔닝은 대용량 테이블과 인덱스를 더 작고 관리하기 쉬운 조각으로 나누는 기법입니다. 쿼리 성능 향상, 데이터 관리 개선, 가용성 향상 등 다양한 이점을 제공합니다.

하지만 파티셔닝은 “테이블을 쪼개면 무조건 빨라진다” 는 기능은 아니다.
핵심은 전체 데이터를 항상 다 읽지 않고, 필요한 파티션만 읽게 만드는 데 있다.

즉 파티셔닝의 진짜 효과는 보통 아래에서 나온다.

조회 시 필요한 파티션만 읽는 파티션 프루닝
오래된 데이터 삭제나 아카이빙을 파티션 단위로 빠르게 수행
대용량 테이블 유지보수를 더 작은 단위로 수행
통계 수집과 인덱스 관리 범위를 줄임

반대로 조회 조건이 파티션 키를 전혀 사용하지 않거나, 파티션 수만 과도하게 많으면 기대한 효과를 못 볼 수도 있다.

언제 파티셔닝을 고려할까

보통 아래 같은 상황에서 파티셔닝을 검토한다.

월별, 일별, 연도별 이력 데이터가 계속 쌓일 때
최근 1개월, 3개월, 1년처럼 기간 조건 조회가 많을 때
오래된 데이터 삭제를 빠르게 처리해야 할 때
배치 작업과 온라인 조회가 같은 대용량 테이블을 함께 사용할 때
특정 지역, 채널, 고객군처럼 명확한 분할 기준이 있을 때

실무에서 가장 흔한 시작점은 날짜 컬럼 기준 Range 또는 Interval 파티셔닝이다.

파티션 키를 잘 고르는 것이 가장 중요

파티션 성능은 파티션 키 선택에 크게 좌우된다.

좋은 파티션 키의 조건은 보통 다음과 같다.

조회 조건에 자주 등장한다.
데이터가 시간 또는 범위 기준으로 자연스럽게 나뉜다.
오래된 데이터를 파티션 단위로 제거하기 쉽다.
특정 파티션에 데이터가 과도하게 몰리지 않는다.

예를 들어 로그, 주문, 이력성 데이터는 created_date, order_date, log_date 같은 날짜 컬럼이 좋은 후보가 된다.
반면 조회에서 거의 쓰이지 않는 컬럼을 파티션 키로 선택하면 프루닝 효과가 거의 없어서 관리 복잡도만 늘 수 있다.

Range 파티션

날짜나 숫자 범위로 파티션을 나눕니다.

-- Range 파티션 생성
CREATE TABLE sales (
  sale_id NUMBER,
  sale_date DATE,
  customer_id NUMBER,
  amount NUMBER
)
PARTITION BY RANGE (sale_date) (
  PARTITION p_2024_q1 VALUES LESS THAN (TO_DATE('2024-04-01', 'YYYY-MM-DD')),
  PARTITION p_2024_q2 VALUES LESS THAN (TO_DATE('2024-07-01', 'YYYY-MM-DD')),
  PARTITION p_2024_q3 VALUES LESS THAN (TO_DATE('2024-10-01', 'YYYY-MM-DD')),
  PARTITION p_2024_q4 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')),
  PARTITION p_max VALUES LESS THAN (MAXVALUE)
);

-- 월별 파티션
CREATE TABLE orders (
  order_id NUMBER,
  order_date DATE,
  customer_id NUMBER
)
PARTITION BY RANGE (order_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(
  PARTITION p_initial VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
);

Range 파티션은 가장 많이 사용하는 방식이다.
특히 날짜 기준 이력 데이터에 잘 맞는다.

예를 들어 주문일, 생성일, 적재일 기준으로 월별 파티션을 나누면:

최근 데이터 조회가 빨라질 수 있고
오래된 월 데이터를 DROP PARTITION 으로 빠르게 정리할 수 있으며
월 단위 통계 관리도 쉬워진다

Interval 파티션은 여기에 자동 생성 기능이 더해진 형태라고 이해하면 된다.
새 월 데이터가 들어올 때 미리 DDL 을 만들지 않아도 자동으로 파티션이 생겨 운영이 편하다.

List 파티션

특정 값 목록으로 파티션을 나눕니다.

-- List 파티션
CREATE TABLE employees_regional (
  employee_id NUMBER,
  first_name VARCHAR2(50),
  last_name VARCHAR2(50),
  country_code VARCHAR2(2)
)
PARTITION BY LIST (country_code) (
  PARTITION p_asia VALUES ('KR', 'JP', 'CN', 'TH'),
  PARTITION p_europe VALUES ('UK', 'FR', 'DE', 'IT'),
  PARTITION p_america VALUES ('US', 'CA', 'MX', 'BR'),
  PARTITION p_other VALUES (DEFAULT)
);

List 파티션은 국가, 지역, 서비스 유형, 채널처럼 값의 종류가 제한적인 경우에 적합하다.

예를 들면:

국가별 데이터 보관 정책이 다를 때
특정 채널만 자주 조회할 때
부서, 사업부, 서비스 코드 기준으로 운영 단위를 나누고 싶을 때

단, 값 종류가 계속 늘어나거나 예측이 어려운 컬럼에는 List 파티션이 점점 관리 부담이 될 수 있다.

Hash 파티션

해시 함수를 사용하여 데이터를 균등하게 분산합니다.

-- Hash 파티션
CREATE TABLE transactions (
  transaction_id NUMBER,
  transaction_date DATE,
  customer_id NUMBER,
  amount NUMBER
)
PARTITION BY HASH (customer_id)
PARTITIONS 8;

-- 파티션 이름 지정
CREATE TABLE logs (
  log_id NUMBER,
  log_date DATE,
  user_id NUMBER,
  message VARCHAR2(4000)
)
PARTITION BY HASH (user_id) (
  PARTITION p_hash1,
  PARTITION p_hash2,
  PARTITION p_hash3,
  PARTITION p_hash4
);

Hash 파티션은 특정 키 기준으로 데이터를 균등하게 분산하는 것이 목적이다.

이 방식은 보통 아래 상황에서 유용하다.

특정 키 값에 데이터가 몰려서 I/O 가 편중될 때
병렬 처리에서 데이터를 고르게 나누고 싶을 때
날짜 범위보다 분산 자체가 더 중요한 테이블일 때

다만 Hash 파티션은 Range 처럼 “지난달 데이터 삭제” 같은 운영 작업에는 덜 직관적이다.
그래서 이력성 데이터에는 단독으로 쓰기보다 Range-Hash 복합 파티션으로 많이 사용한다.

Composite 파티션

여러 파티셔닝 방법을 조합합니다.

-- Range-Hash 복합 파티션
CREATE TABLE sales_data (
  sale_id NUMBER,
  sale_date DATE,
  customer_id NUMBER,
  amount NUMBER
)
PARTITION BY RANGE (sale_date)
SUBPARTITION BY HASH (customer_id)
SUBPARTITIONS 4
(
  PARTITION p_2024_q1 VALUES LESS THAN (TO_DATE('2024-04-01', 'YYYY-MM-DD')),
  PARTITION p_2024_q2 VALUES LESS THAN (TO_DATE('2024-07-01', 'YYYY-MM-DD')),
  PARTITION p_2024_q3 VALUES LESS THAN (TO_DATE('2024-10-01', 'YYYY-MM-DD')),
  PARTITION p_2024_q4 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))
);

-- Range-List 복합 파티션
CREATE TABLE regional_sales (
  sale_id NUMBER,
  sale_date DATE,
  country_code VARCHAR2(2),
  amount NUMBER
)
PARTITION BY RANGE (sale_date)
SUBPARTITION BY LIST (country_code) (
  PARTITION p_2024_q1 VALUES LESS THAN (TO_DATE('2024-04-01', 'YYYY-MM-DD'))
    (SUBPARTITION p_2024_q1_asia VALUES ('KR', 'JP', 'CN'),
     SUBPARTITION p_2024_q1_europe VALUES ('UK', 'FR', 'DE'),
     SUBPARTITION p_2024_q1_other VALUES (DEFAULT)),
  PARTITION p_2024_q2 VALUES LESS THAN (TO_DATE('2024-07-01', 'YYYY-MM-DD'))
    (SUBPARTITION p_2024_q2_asia VALUES ('KR', 'JP', 'CN'),
     SUBPARTITION p_2024_q2_europe VALUES ('UK', 'FR', 'DE'),
     SUBPARTITION p_2024_q2_other VALUES (DEFAULT))
);

복합 파티션은 “운영 기준” 과 “분산 기준” 을 동시에 가져가고 싶을 때 사용한다.

예를 들어:

1차로 sale_date 기준 월별 관리
2차로 customer_id 기준 분산

이런 식이면 월별 삭제/아카이빙은 유지하면서도 특정 파티션 내부의 데이터 쏠림을 줄일 수 있다.

실무에서 자주 보이는 형태는 다음과 같다.

Range-Hash: 기간 관리 + 데이터 분산
Range-List: 기간 관리 + 지역/코드 분리

구조는 강력하지만, 서브파티션까지 들어가면 관리 복잡도가 빠르게 증가하므로 정말 필요한 경우에만 쓰는 편이 좋다.

파티션 프루닝

쿼리 실행 시 불필요한 파티션을 제외하여 성능을 향상시킵니다.

-- 파티션 프루닝 활용
SELECT * FROM sales
WHERE sale_date >= DATE '2024-04-01'
  AND sale_date < DATE '2024-07-01';

-- 실행 계획에서 확인
EXPLAIN PLAN FOR
SELECT * FROM sales
WHERE sale_date >= DATE '2024-04-01'
  AND sale_date < DATE '2024-07-01';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 파티션 프루닝이 적용되면:
-- "Pstart: 2  Pstop: 2" (파티션 2만 스캔)

파티셔닝 성능의 핵심은 대부분 이 파티션 프루닝 에 있다.
옵티마이저가 조건을 보고 “읽어야 할 파티션만 읽는” 상태가 되어야 효과가 난다.

예를 들어 월별 파티션 테이블에서 2024년 4월 데이터만 조회하면, 전체 테이블이 아니라 해당 월 파티션만 읽게 된다.
이 차이는 데이터가 커질수록 매우 크게 벌어진다.

프루닝이 잘 안 되는 대표적인 경우

파티셔닝을 했어도 SQL 작성 방식이 나쁘면 프루닝 효과가 약해질 수 있다.

-- 나쁜 예: 파티션 키에 함수 적용
SELECT *
FROM sales
WHERE TRUNC(sale_date) = DATE '2024-04-15';

-- 좋은 예: 범위 조건으로 작성
SELECT *
FROM sales
WHERE sale_date >= DATE '2024-04-15'
  AND sale_date < DATE '2024-04-16';

또한 아래 경우도 주의한다.

파티션 키에 암묵적 형변환이 발생하는 경우
바인드 변수와 통계 상태 때문에 프루닝이 비효율적으로 되는 경우
파티션 키가 아닌 다른 컬럼만으로 조회하는 경우

즉 파티션 테이블에서는 SQL 작성 방식도 같이 튜닝해야 한다.

파티션 관리

-- 새 파티션 추가
ALTER TABLE sales ADD PARTITION p_2025_q1
  VALUES LESS THAN (TO_DATE('2025-04-01', 'YYYY-MM-DD'));

-- 파티션 삭제
ALTER TABLE sales DROP PARTITION p_2024_q1;

-- 파티션 Truncate (데이터만 삭제)
ALTER TABLE sales TRUNCATE PARTITION p_2024_q1;

-- 파티션 분할
ALTER TABLE sales SPLIT PARTITION p_max
  AT (TO_DATE('2025-04-01', 'YYYY-MM-DD'))
  INTO (PARTITION p_2025_q1, PARTITION p_max);

-- 파티션 병합
ALTER TABLE sales MERGE PARTITIONS p_2024_q1, p_2024_q2
  INTO PARTITION p_2024_h1;

-- 파티션 교환
-- 비파티션 테이블과 파티션 교환 (빠른 데이터 로드)
CREATE TABLE sales_staging (
  sale_id NUMBER,
  sale_date DATE,
  customer_id NUMBER,
  amount NUMBER
);

ALTER TABLE sales EXCHANGE PARTITION p_2024_q1
  WITH TABLE sales_staging
  INCLUDING INDEXES
  WITHOUT VALIDATION;

파티션 관리의 가장 큰 장점은 대량 데이터를 행 단위가 아니라 파티션 단위로 다룰 수 있다는 점이다.

예를 들어:

오래된 데이터 삭제는 DELETE 대신 DROP PARTITION
데이터 초기화는 DELETE 대신 TRUNCATE PARTITION
대량 적재는 스테이징 테이블과 EXCHANGE PARTITION

이 방식은 Undo/Redo 부담을 크게 줄이고, 작업 시간을 매우 짧게 만들 수 있다.

특히 EXCHANGE PARTITION 은 실무에서 매우 강력하다.
스테이징 테이블에 미리 데이터를 적재하고 검증한 뒤, 거의 메타데이터 수준으로 본 테이블 파티션과 교체할 수 있기 때문이다.

파티션 인덱스

-- Local 인덱스 (파티션별 인덱스)
CREATE INDEX idx_sales_date ON sales (sale_date) LOCAL;

-- Global 인덱스 (전역 인덱스)
CREATE INDEX idx_sales_customer ON sales (customer_id) GLOBAL
PARTITION BY RANGE (customer_id) (
  PARTITION p_cust1 VALUES LESS THAN (1000),
  PARTITION p_cust2 VALUES LESS THAN (2000),
  PARTITION p_cust3 VALUES LESS THAN (MAXVALUE)
);

-- Local Prefixed 인덱스
CREATE INDEX idx_sales_composite ON sales (sale_date, customer_id) LOCAL;

-- Local Non-Prefixed 인덱스
CREATE INDEX idx_sales_amount ON sales (amount) LOCAL;

파티션 테이블에서는 인덱스 전략도 같이 설계해야 한다.

LOCAL 인덱스: 테이블 파티션 구조를 따라감
GLOBAL 인덱스: 테이블 파티션과 독립적으로 구성

보통 운영 편의성은 LOCAL 인덱스가 더 좋고, 특정 조회 패턴 대응은 GLOBAL 인덱스가 유리한 경우가 있다.
이 부분은 인덱스 생성과 파티션 테이블 ONLINE REBUILD 정리 문서와 함께 보면 이해가 더 쉽다.

특히 파티션 유지보수가 잦은 테이블에서는 인덱스 영향까지 같이 생각해야 한다.

파티션 정보 조회

-- 파티션 목록 조회
SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
WHERE table_name = 'SALES'
ORDER BY partition_position;

-- 서브파티션 조회
SELECT table_name, partition_name, subpartition_name, num_rows
FROM user_tab_subpartitions
WHERE table_name = 'SALES_DATA'
ORDER BY subpartition_position;

-- 파티션별 통계
SELECT partition_name,
       num_rows,
       blocks,
       avg_row_len,
       last_analyzed
FROM user_tab_partitions
WHERE table_name = 'SALES';

-- 파티션별 크기
SELECT segment_name, partition_name,
       ROUND(bytes/1024/1024, 2) as size_mb
FROM user_segments
WHERE segment_name = 'SALES'
  AND partition_name IS NOT NULL
ORDER BY partition_name;

Reference 파티션

부모 테이블의 파티션 키를 참조합니다.

-- 부모 테이블 (Range 파티션)
CREATE TABLE orders (
  order_id NUMBER PRIMARY KEY,
  order_date DATE,
  customer_id NUMBER
)
PARTITION BY RANGE (order_date) (
  PARTITION p_2024_q1 VALUES LESS THAN (TO_DATE('2024-04-01', 'YYYY-MM-DD')),
  PARTITION p_2024_q2 VALUES LESS THAN (TO_DATE('2024-07-01', 'YYYY-MM-DD'))
);

-- 자식 테이블 (Reference 파티션)
CREATE TABLE order_items (
  item_id NUMBER,
  order_id NUMBER,
  product_id NUMBER,
  quantity NUMBER,
  CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES orders(order_id)
)
PARTITION BY REFERENCE (fk_order);

Reference 파티션은 부모-자식 관계가 강한 모델에서 유용하다.
부모 테이블의 파티션 구조를 자식이 그대로 따라가므로, 부모/자식 데이터를 같은 파티션 단위로 관리하기 쉬워진다.

예를 들어 주문 헤더와 주문 상세처럼 항상 같이 조회하고 같이 보관 주기를 맞춰야 하는 테이블에 잘 맞는다.

Interval 파티션

자동으로 파티션을 생성합니다.

-- Interval 파티션 (11g 이상)
CREATE TABLE measurements (
  measurement_id NUMBER,
  measurement_date DATE,
  sensor_id NUMBER,
  value NUMBER
)
PARTITION BY RANGE (measurement_date)
INTERVAL (NUMTODSINTERVAL(1, 'DAY'))
(
  PARTITION p_initial VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
);

-- 자동으로 일별 파티션 생성
INSERT INTO measurements VALUES (1, DATE '2024-06-15', 100, 25.5);
-- p_initial 이후 파티션이 자동 생성됨

Interval 파티션은 운영 자동화 측면에서 매우 편하다.
월별 또는 일별 데이터가 계속 들어오는데, 파티션 추가 DDL 을 매번 수동으로 관리하고 싶지 않을 때 좋은 선택이다.

다만 자동 생성된 파티션도 결국 통계, 인덱스, 보관 정책은 여전히 운영자가 관리해야 한다.

파티션 통계 관리

-- 파티션별 통계 수집
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'HR',
  tabname => 'SALES',
  partname => 'P_2024_Q1',
  granularity => 'PARTITION'
);

-- 전체 파티션 통계 수집
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'HR',
  tabname => 'SALES',
  granularity => 'ALL'
);

-- 증분 통계 (Global + Partition)
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'HR',
  tabname => 'SALES',
  granularity => 'GLOBAL AND PARTITION'
);

파티션 테이블은 통계 관리도 일반 테이블보다 더 신경 써야 한다.

특정 파티션만 데이터가 크게 바뀌었는지
글로벌 통계와 파티션 통계가 모두 필요한지
증분 통계가 유리한지

대용량 이력성 테이블에서는 전체 통계를 매번 다시 모으는 것보다, 변경된 파티션 중심으로 관리하는 전략이 더 현실적일 때가 많다.

파티셔닝의 장점과 주의점

장점

필요한 파티션만 읽으면 조회 성능이 좋아질 수 있다.
삭제/아카이빙/적재를 파티션 단위로 빠르게 처리할 수 있다.
대용량 테이블 유지보수를 더 작은 단위로 나눌 수 있다.
통계, 백업, 인덱스 관리 범위를 줄일 수 있다.

주의점

파티션 키를 잘못 고르면 효과가 거의 없다.
파티션 수가 너무 많으면 오히려 관리 복잡도가 커진다.
인덱스 전략을 같이 설계하지 않으면 운영이 불편해질 수 있다.
프루닝이 안 되는 SQL 작성 습관이면 기대 성능이 나오지 않는다.
테스트 환경과 운영 환경의 데이터 분포 차이로 효과가 다르게 보일 수 있다.

어떤 파티셔닝 방식을 고를까

간단히 정리하면 다음 기준으로 시작하면 된다.

날짜 기반 이력 데이터: Range 또는 Interval
값 종류가 명확한 코드성 분리: List
데이터 분산과 병렬 처리: Hash
기간 관리 + 분산: Range-Hash
기간 관리 + 지역/코드 구분: Range-List
부모/자식 구조 동기화: Reference

파티션 튜닝 체크리스트

파티션 키 선택 (조회 패턴 고려)
파티션 수 적절성 검토
파티션 프루닝 활용 확인
Local vs Global 인덱스 선택
파티션별 통계 정보 관리
오래된 파티션 삭제/아카이빙
Interval 파티션 활용 검토
Reference 파티션 활용 가능성

실전 예제

-- 예제 1: 이력 데이터 관리
CREATE TABLE user_activity_log (
  log_id NUMBER,
  log_date DATE,
  user_id NUMBER,
  activity_type VARCHAR2(50),
  details VARCHAR2(4000)
)
PARTITION BY RANGE (log_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(
  PARTITION p_initial VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
);

-- 로컬 인덱스 생성
CREATE INDEX idx_log_user ON user_activity_log (user_id) LOCAL;

-- 오래된 파티션 삭제 (3개월 이전 데이터)
DECLARE
  v_partition_name VARCHAR2(30);
BEGIN
  FOR rec IN (
    SELECT partition_name
    FROM user_tab_partitions
    WHERE table_name = 'USER_ACTIVITY_LOG'
      AND partition_name != 'P_INITIAL'
      AND TO_DATE(
            TRIM(BOTH '''' FROM
              REGEXP_SUBSTR(high_value, '''[^'']+''')),
            'YYYY-MM-DD'
          ) < ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -3)
  ) LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE user_activity_log DROP PARTITION ' || rec.partition_name;
  END LOOP;
END;
/

-- 예제 2: 다국가 데이터 관리
CREATE TABLE customer_orders (
  order_id NUMBER,
  order_date DATE,
  country_code VARCHAR2(2),
  customer_id NUMBER,
  amount NUMBER
)
PARTITION BY RANGE (order_date)
SUBPARTITION BY LIST (country_code) (
  PARTITION p_2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))
    (SUBPARTITION p_2024_kr VALUES ('KR'),
     SUBPARTITION p_2024_us VALUES ('US'),
     SUBPARTITION p_2024_others VALUES (DEFAULT))
);

-- 국가별, 연도별 조회 최적화
SELECT * FROM customer_orders
WHERE order_date >= DATE '2024-01-01'
  AND order_date < DATE '2025-01-01'
  AND country_code = 'KR';
-- 파티션 프루닝: p_2024_kr만 스캔

위 예제는 파티셔닝의 장점을 잘 보여준다.

연도 조건으로 상위 파티션을 줄이고
국가 조건으로 서브파티션까지 줄인다

즉 대량 테이블이라도 실제로는 필요한 구간만 읽게 만들 수 있다.

정리

파티셔닝의 핵심은 데이터를 물리적으로 나누는 것 자체가 아니라, 조회와 운영 작업을 더 작은 단위로 수행하게 만드는 데 있다.

성능 관점에서는 파티션 프루닝
운영 관점에서는 DROP/TRUNCATE/EXCHANGE PARTITION
설계 관점에서는 파티션 키 선택
인덱스 관점에서는 LOCAL / GLOBAL 전략

특히 이력성 대용량 테이블에서는 파티셔닝이 성능과 운영 양쪽에 큰 차이를 만들 수 있다.
다만 파티셔닝만 적용한다고 자동으로 빨라지는 것은 아니므로, 조회 패턴, SQL 작성 방식, 인덱스, 통계까지 함께 설계하는 것이 중요하다.

딴짓하는 개발자

DevelopOracle