Oracle에서 파티셔닝 테이블은 대용량 데이터를 효율적으로 관리하고 성능을 향상시키며 유지 관리를 용이하게 만드는 강력한 전략입니다. 아래는 Oracle 파티셔닝 테이블의 모범 사례입니다:
1. 데이터와 작업 부하 이해하기
• 데이터 분포, 쿼리 패턴, 데이터 접근 빈도를 분석하세요. • 파티셔닝이 적합한 경우: • 범위, 리스트, 해시 키로 나누어야 하는 대용량 테이블. • 시간 기반 데이터 또는 논리적 그룹화가 필요한 데이터. • 데이터 아카이빙 또는 삭제를 빠르게 수행해야 하는 경우.
2. 적절한 파티셔닝 전략 선택
2.1 범위 파티셔닝 (Range Partitioning)
• 순차적 데이터 또는 시간 기반 데이터에 가장 적합. • 특정 열의 값 범위를 기준으로 데이터를 나눕니다(예: 날짜, 숫자 범위). • 로그, 거래 기록, 히스토리 데이터 등에 적합.
CREATE TABLE sales (
sale_id NUMBER, sale_date DATE, amount NUMBER
) PARTITION BY RANGE (sale_date) (
PARTITION p_2023_q1 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')),
PARTITION p_2023_q2 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD')),
PARTITION p_2023_q3 VALUES LESS THAN (TO_DATE('2023-10-01', 'YYYY-MM-DD')),
PARTITION p_max VALUES LESS THAN (MAXVALUE)
);
2.2 리스트 파티셔닝 (List Partitioning)
• 특정 이산 값(예: 지역, 카테고리)으로 그룹화된 데이터에 적합. • 리스트를 기준으로 특정 파티션을 대상으로 쿼리할 수 있음.
CREATE TABLE orders (
order_id NUMBER, region VARCHAR2(10), order_date DATE
) PARTITION BY LIST (region) (
PARTITION p_us VALUES ('US'),
PARTITION p_europe VALUES ('EUROPE'),
PARTITION p_asia VALUES ('ASIA')
);
2.3 해시 파티셔닝 (Hash Partitioning)
• 자연스러운 범위나 리스트로 그룹화할 수 없는 데이터를 고르게 분배하기에 적합. • 핫스팟을 방지하고 부하를 균등하게 분산.
CREATE TABLE employees (
emp_id NUMBER, dept_id NUMBER, emp_name VARCHAR2(100)
) PARTITION BY HASH (dept_id) PARTITIONS 4;
2.4 복합 파티셔닝 (Composite Partitioning)
• 두 가지 파티셔닝 전략을 결합(예: 범위 + 해시, 범위 + 리스트). • 복잡한 시나리오에 적합(예: 시간 기반 범위 파티셔닝 + 부하 분산을 위한 해시).
CREATE TABLE sales (
sale_id NUMBER, sale_date DATE, region VARCHAR2(10)
) PARTITION BY RANGE (sale_date) SUBPARTITION BY HASH (region) SUBPARTITIONS 4 (
PARTITION p_2023_q1 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')),
PARTITION p_2023_q2 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD'))
);
3. 쿼리 작업 부하에 맞게 파티셔닝 정렬
• 파티션을 자주 사용하는 쿼리 조건(예: 날짜, 지역)에 맞게 설계하세요. • 불필요한 파티션 스캔을 방지하도록 파티셔닝 전략을 선택하세요.
4. 인덱스 파티셔닝
• 로컬 인덱스(Local Index): 파티션당 하나의 인덱스. 파티션 관리가 쉬우며 파티션 프루닝에 유리. • 글로벌 인덱스(Global Index): 모든 파티션에 걸친 단일 인덱스. 파티션 간 쿼리에 유용하지만 유지보수가 복잡.
-- 로컬 인덱스 CREATE INDEX idx_sales_date ON sales(sale_date) LOCAL;
-- 글로벌 인덱스 CREATE INDEX idx_sales_amount ON sales(amount) GLOBAL;
5. 파티션 프루닝(Partition Pruning) 사용
• 쿼리에서 관련 파티션만 스캔하도록 설계하여 파티션 프루닝을 최대한 활용하세요. • 쿼리 조건이 파티션 키와 일치하도록 설계.
6. 자동 파티셔닝 관리
• Interval Partitioning을 사용하여 새로운 데이터가 추가될 때 자동으로 파티션을 생성하세요. 시간 기반 데이터에 적합.
CREATE TABLE logs (
log_id NUMBER, log_date DATE, message VARCHAR2(4000)
) PARTITION BY RANGE (log_date) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) (
PARTITION p_start VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
);
7. 과도한 파티션 생성 방지
• 지나치게 세분화된 파티션은 메타데이터 오버헤드를 증가시킬 수 있습니다. • 워크로드와 쿼리에 맞게 적절한 파티션 수를 유지하세요.
8. 파티션 유지보수
• 파티션 분할(Split): 큰 파티션을 더 작은 파티션으로 나눕니다. • 파티션 병합(Merge): 더 이상 분리할 필요가 없는 인접 파티션을 결합합니다. • 파티션 삭제(Drop): 아카이빙 또는 데이터 삭제가 간편해짐.
ALTER TABLE sales DROP PARTITION p_2023_q1;
9. 파티션 성능 모니터링
• SQL 모니터링, AWR 리포트 등을 활용해 파티션이 성능을 개선하고 있는지 확인하세요. • DBA_TAB_PARTITIONS 및 DBA_TAB_SUBPARTITIONS를 통해 파티션 상태를 점검하세요.
10. 파티션 키 설계 원칙
• 데이터가 고르게 분배되고 불균형 파티션이 발생하지 않도록 파티션 키를 선택하세요. • 쿼리 패턴을 지원하고 자주 업데이트되지 않는 열을 선택하세요.
11. 파티션 작업의 영향 최소화
• 파티션 작업(분할, 병합, 삭제)은 특히 글로벌 인덱스와 함께 사용 시 성능에 영향을 줄 수 있습니다. • 온라인 파티션 유지보수를 활용하여 다운타임을 최소화하세요:
ALTER TABLE sales SPLIT PARTITION p_max AT (TO_DATE('2024-01-01', 'YYYY-MM-DD')) INTO (PARTITION p_2024_q1, PARTITION p_max) ONLINE;
12. 파티션 통계
• 파티션 수준 통계를 수집하여 옵티마이저가 정확한 쿼리 계획을 생성하도록 보장합니다.
BEGIN
DBMS_STATS.GATHER_TABLE_STATS('schema_name', 'table_name', PARTNAME => 'partition_name');
END;
13. 읽기 및 쓰기 작업 고려
• 읽기 작업이 많은 경우 범위 또는 리스트 파티셔닝으로 선택도를 높이세요. • 쓰기 작업이 많은 경우 해시 파티셔닝으로 부하를 균등하게 분산하세요.
14. 파티션을 활용한 데이터 아카이빙
• 파티션을 활용해 데이터 아카이빙과 삭제를 간소화하세요: • 행을 삭제하는 대신 오래된 파티션을 삭제.
ALTER TABLE logs DROP PARTITION p_2023;
• 파티션을 내보내 백업 또는 외부 아카이빙을 수행.
위의 모범 사례를 따르면, 데이터 모델, 쿼리 패턴, 성능 요구사항에 맞는 파티션 테이블을 설계할 수 있습니다. 파티셔닝은 데이터 관리와 확장성을 극대화하는 데 필수적인 도구입니다.