Oracle - 파티셔닝 활용

Oracle 파티셔닝 활용

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

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

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'))
);

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)
);

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
);

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))
);

파티션 프루닝

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

-- 파티션 프루닝 활용
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만 스캔)

파티션 관리

-- 새 파티션 추가
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;

파티션 인덱스

-- 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;

파티션 정보 조회

-- 파티션 목록 조회
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);

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 이후 파티션이 자동 생성됨

파티션 통계 관리

-- 파티션별 통계 수집
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'
);

파티션 튜닝 체크리스트

• 파티션 키 선택 (조회 패턴 고려)
• 파티션 수 적절성 검토
• 파티션 프루닝 활용 확인
• 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만 스캔

관련 문서

• 쿼리 튜닝 가이드
• 인덱스 최적화
• 통계 정보 관리