파티셔닝을 추가하려면?

테이블 생성이 이미 끝난 상태에서 나중에 파티셔닝을 추가하려면 어떻게 해야할까?

 

만약 아래와 같이 transactions (거래) 테이블이 생성되어있을 경우, 처리하는 방법을 알아보자.

CREATE TABLE transactions (
    trans_id NUMBER PRIMARY KEY,
    asset_id NUMBER NOT NULL,
    owner_id NUMBER NOT NULL,
    trans_date DATE DEFAULT SYSDATE,
    trans_price NUMBER NOT NULL,
    CONSTRAINT fk_asset_id FOREIGN KEY (asset_id) REFERENCES assets (asset_id),
    CONSTRAINT fk_owner_id FOREIGN KEY (owner_id) REFERENCES owners (owner_id)
);

 

 

 

1. 파티셔닝을 포함한 임시 테이블(transactions_temp) 생성

CREATE TABLE transactions_temp (
    trans_id NUMBER PRIMARY KEY,
    asset_id NUMBER NOT NULL,
    owner_id NUMBER NOT NULL,
    trans_date DATE DEFAULT SYSDATE,
    trans_price NUMBER NOT NULL,
    CONSTRAINT fk_asset_id_temp FOREIGN KEY (asset_id) REFERENCES assets (asset_id),
    CONSTRAINT fk_owner_id_temp FOREIGN KEY (owner_id) REFERENCES owners (owner_id)
)
PARTITION BY RANGE (trans_date) (
    PARTITION p_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
    PARTITION p_2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')),
    PARTITION p_2025 VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD')),
    PARTITION p_max VALUES LESS THAN (MAXVALUE)
);

 

2. 기존 데이터 복사

INSERT INTO transactions_temp
SELECT * FROM transactions;

 

3. 기존 테이블 삭제 및 이름 변경

DROP TABLE transactions;
RENAME transactions_temp TO transactions;

 

4. 인덱스 및 제약 재생성

ALTER TABLE transactions
ADD CONSTRAINT fk_asset_id FOREIGN KEY (asset_id) REFERENCES assets (asset_id);
ALTER TABLE transactions
ADD CONSTRAINT fk_owner_id FOREIGN KEY (owner_id) REFERENCES owners (owner_id);