Oracle Database 강좌

오라클 대용량 테이블 성능 향상: 테이블 파티셔닝 재정의를 통한 최적화

_Blue_Sky_ 2024. 12. 21. 00:03
728x90
728x90

 

Oracle 데이터베이스에서 대용량 테이블의 성능을 향상시키기 위한 효과적인 방법 중 하나는 테이블 파티셔닝입니다. 테이블을 특정 기준에 따라 여러 개의 작은 조각으로 나누어 관리함으로써, 데이터 액세스 성능을 향상시키고, 디스크 I/O를 분산시켜 시스템 부하를 줄일 수 있습니다. 특히, 데이터가 지속적으로 증가하는 환경에서는 테이블 파티셔닝을 통해 데이터베이스의 성능 저하를 방지하고, 유지 보수를 용이하게 할 수 있습니다.

테이블 파티셔닝 재정의란?

테이블 파티셔닝 재정의는 기존에 생성된 테이블의 파티셔닝 방식을 변경하거나, 새로운 파티션을 추가 또는 삭제하는 작업을 의미합니다. 데이터베이스 환경이 변화하거나, 데이터 액세스 패턴이 변경될 경우, 기존 파티셔닝 방식이 더 이상 효율적이지 않을 수 있습니다. 이러한 경우, 테이블 파티셔닝을 재정의하여 성능을 최적화할 수 있습니다.

테이블 파티셔닝 재정의 과정

  1. 중간 테이블 생성: 기존 테이블의 구조를 복사하여 새로운 테이블을 생성합니다. 이때, 변경하고자 하는 파티셔닝 방식을 적용합니다.
  2. 데이터 복사: 기존 테이블의 데이터를 새로 생성한 테이블로 복사합니다.
  3. 테이블 교체: 기존 테이블을 삭제하고, 새로 생성한 테이블의 이름을 기존 테이블의 이름으로 변경합니다.
728x90

오라클(Oracle) 데이터베이스에서 대용량 테이블의 성능을 향상시키기 위해 **테이블스페이스 분할(Table Partitioning)**을 사용하는 방법은 매우 효과적인 기법 중 하나입니다. 이 기법은 데이터를 여러 개의 파티션으로 나누어 관리함으로써 성능을 최적화하고, 유지 보수를 용이하게 만듭니다. 주요 기법은 다음과 같습니다:

1. 테이블 파티셔닝 개념

  • **파티셔닝(Partitioning)**은 테이블을 물리적으로 작은 조각으로 나누는 기능입니다.
  • 각 파티션은 별도의 테이블스페이스에 저장할 수 있어, I/O 부하를 분산시키고 검색 속도를 높이는 데 기여합니다.

2. 주요 파티셔닝 유형

(1) Range Partitioning (범위 분할)

  • 데이터를 특정 범위에 따라 나눕니다.
  • 예: 날짜, 숫자와 같은 순서형 데이터
CREATE TABLE orders (
    order_id NUMBER,
    order_date DATE,
    amount NUMBER
)
PARTITION BY RANGE (order_date) (
    PARTITION p1 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')),
    PARTITION p2 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

(2) List Partitioning (리스트 분할)

  • 데이터를 특정 값의 목록에 따라 나눕니다.
  • 예: 지역, 카테고리
CREATE TABLE employees (
    emp_id NUMBER,
    dept_name VARCHAR2(50)
)
PARTITION BY LIST (dept_name) (
    PARTITION p_sales VALUES ('SALES'),
    PARTITION p_hr VALUES ('HR'),
    PARTITION p_it VALUES ('IT')
);

(3) Hash Partitioning (해시 분할)

  • 데이터를 고르게 분산하기 위해 해시 함수를 사용합니다.
  • 데이터 패턴이 균일하지 않을 때 유용합니다.
CREATE TABLE large_table (
    id NUMBER,
    data VARCHAR2(100)
)
PARTITION BY HASH (id)
PARTITIONS 4;

(4) Composite Partitioning (복합 분할)

  • Range와 Hash, 또는 Range와 List를 조합하여 사용합니다.
  • 예: 먼저 날짜로 Range 분할, 이후 해시로 분할.
CREATE TABLE composite_table (
    id NUMBER,
    order_date DATE,
    amount NUMBER
)
PARTITION BY RANGE (order_date)
SUBPARTITION BY HASH (id)
SUBPARTITIONS 4 (
    PARTITION p1 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')),
    PARTITION p2 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
);

3. 테이블스페이스 분리

  • 각 파티션을 별도의 테이블스페이스에 저장하여 디스크 I/O 부하를 줄이고 병렬 처리를 지원합니다.
CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    amount NUMBER
)
PARTITION BY RANGE (sale_date) (
    PARTITION p1 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')) TABLESPACE ts1,
    PARTITION p2 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')) TABLESPACE ts2,
    PARTITION p3 VALUES LESS THAN (MAXVALUE) TABLESPACE ts3
);

4. 파티셔닝의 장점

  1. 성능 향상: 특정 파티션만 조회하므로 검색 속도가 증가합니다.
  2. 병렬 처리: 각 파티션에서 병렬 처리를 수행할 수 있습니다.
  3. 관리 용이성: 파티션 단위로 데이터를 백업, 복구, 유지 보수할 수 있습니다.
  4. 효율적 공간 관리: 테이블스페이스를 분리하여 디스크 공간 활용을 최적화합니다.

5. 주의할 점

  • 파티션 키 선택은 데이터 액세스 패턴과 밀접하게 연관되어야 합니다.
  • 잘못된 파티셔닝 전략은 오히려 성능 저하를 초래할 수 있습니다.
  • 파티션의 수가 너무 많으면 관리 복잡성이 증가할 수 있습니다.
728x90

오라클에서 파티셔닝을 재조정(Partition Redefinition)하는 것은 기존 테이블을 새로운 파티셔닝 방식으로 변경하거나 파티션을 추가/삭제하여 데이터 구조를 최적화하는 작업입니다. 주로 DBMS_REDEFINITION 패키지를 사용하며, 아래는 단계별로 진행하는 예시입니다.

예제 시나리오

  1. 기존 테이블 sales는 파티션이 없는 상태입니다.
  2. sales 테이블을 **범위 파티셔닝(Range Partitioning)**으로 변경하려고 합니다.
  3. 데이터 가용성을 유지하면서 작업을 수행합니다.

1. 기존 테이블 생성

CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    amount NUMBER
);

-- 샘플 데이터 삽입
INSERT INTO sales VALUES (1, TO_DATE('2023-01-01', 'YYYY-MM-DD'), 100);
INSERT INTO sales VALUES (2, TO_DATE('2023-06-01', 'YYYY-MM-DD'), 200);
INSERT INTO sales VALUES (3, TO_DATE('2024-01-01', 'YYYY-MM-DD'), 300);
COMMIT;

2. 중간 테이블 생성

파티셔닝된 새로운 테이블을 정의합니다.

CREATE TABLE sales_partitioned (
    sale_id NUMBER,
    sale_date DATE,
    amount NUMBER
)
PARTITION BY RANGE (sale_date) (
    PARTITION p1 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD')),
    PARTITION p2 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

3. DBMS_REDEFINITION 패키지 사용

(1) Redefinition 시작

BEGIN
    DBMS_REDEFINITION.START_REDEF_TABLE(
        uname          => 'YOUR_SCHEMA',
        orig_table     => 'sales',
        int_table      => 'sales_partitioned'
    );
END;
/

(2) 데이터 복사

  • 기존 테이블의 데이터를 새 테이블로 복사합니다.
BEGIN
    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
        uname           => 'YOUR_SCHEMA',
        orig_table      => 'sales',
        int_table       => 'sales_partitioned',
        copy_indexes    => 1,
        copy_triggers   => 1,
        copy_constraints => 1,
        copy_privileges => 0
    );
END;
/

(3) Redefinition 완료

BEGIN
    DBMS_REDEFINITION.FINISH_REDEF_TABLE(
        uname          => 'YOUR_SCHEMA',
        orig_table     => 'sales',
        int_table      => 'sales_partitioned'
    );
END;
/

4. 결과 확인

(1) 새로운 테이블 확인

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

(2) 데이터 확인

SELECT * FROM sales;

5. 주의사항

  1. 데이터 트랜잭션 처리: 재정의 도중에도 기존 테이블에 대한 트랜잭션은 허용됩니다.
    재정의 완료 시 데이터 동기화가 이루어집니다.
  2. 예비 공간 확보: 새로운 테이블을 생성하고 데이터 복사를 수행하므로, 충분한 테이블스페이스가 필요합니다.
  3. 제약 조건 및 인덱스: 기존 테이블의 제약 조건과 인덱스는 적절히 복사해야 합니다.

위 과정은 파티셔닝 없이 존재하던 테이블을 파티셔닝 구조로 변경하는 예제입니다. 이미 파티셔닝된 테이블의 파티션을 재조정(예: 파티션 추가/병합)하는 경우도 필요하면 설명드리겠습니다.

 

설명에서 "기존 테이블", "새 테이블", 그리고 "중간 테이블"은 DBMS_REDEFINITION 패키지에서 사용하는 개념입니다. 각각의 의미는 다음과 같습니다:

1. 기존 테이블 (Original Table)

  • 현재 운영 중인 테이블로, 데이터와 트랜잭션이 활성 상태인 테이블입니다.
  • 재정의 작업이 시작되기 전까지 애플리케이션에서 사용되고 있던 테이블입니다.
  • 예: sales

2. 중간 테이블 (Intermediate Table)

  • 기존 테이블을 대체하기 위해 새로운 구조로 설계된 테이블입니다.
  • 여기에서 새로운 파티션 설계를 적용하거나 다른 스키마 구조를 반영할 수 있습니다.
  • 기존 테이블의 데이터를 이 테이블로 복사하며, 이 테이블은 완전히 새로 정의됩니다.
  • 예: sales_partitioned

3. 새로운 테이블 (Final Table)

  • DBMS_REDEFINITION 작업이 완료된 후, 중간 테이블이 기존 테이블의 역할을 대신합니다.
  • 기존 테이블과 이름이 같아지며, 애플리케이션은 이 테이블을 투명하게 사용하게 됩니다.
  • 따라서 최종적으로 기존 테이블과 교체됩니다.

DBMS_REDEFINITION의 흐름 요약

  1. 중간 테이블 생성: 새로운 구조를 가진 테이블을 설계합니다.
    • 이 테이블이 "중간 테이블" 역할을 합니다.
  2. 기존 테이블 데이터를 중간 테이블로 복사: 데이터와 인덱스, 트리거 등을 복사합니다.
  3. 테이블 교체:
    • 기존 테이블과 중간 테이블의 역할을 교환하여 중간 테이블이 새로운 테이블이 됩니다.

DBMS_REDEFINITION 후의 상태

  • 기존 테이블은 더 이상 사용되지 않으며, 필요에 따라 삭제할 수 있습니다.
  • 중간 테이블이 새로운 이름을 가지며, 최종 테이블로 동작합니다.

시각화 

역할 테이블 이름 (예시) 설명
기존 테이블 sales 애플리케이션이 사용 중인 테이블
중간 테이블 sales_partitioned 새 구조를 가진 테이블 (범위 파티셔닝 등)
새로운 테이블 sales 작업 완료 후, 최종적으로 운영에 사용됨

 

 

네, 작업이 끝난 후 **중간 테이블(Intermediate Table)**은 더 이상 필요하지 않으므로 보통 삭제(DROP)하는 것이 일반적입니다. 하지만, 삭제하기 전에 반드시 다음 사항을 확인하는 것이 중요합니다.

1. 중간 테이블 삭제 필요성

  • 중간 테이블은 기존 테이블과 데이터를 동기화하고 교체 작업을 위해 사용되었으므로, 최종 테이블로 사용되지 않습니다.
  • 교체 작업이 성공적으로 완료되었고, 데이터와 제약 조건 등이 제대로 복사되었는지 확인한 후 삭제합니다.

2. 중간 테이블 삭제 절차

(1) 교체 작업 성공 확인

  • 새로운 테이블 구조(sales)가 정상 작동하는지 확인합니다: 
    SELECT * FROM sales;

(2) 중간 테이블 삭제

  • 중간 테이블을 안전하게 삭제합니다: 
    DROP TABLE sales_partitioned;

3. 중간 테이블 삭제 전에 고려할 점

  • 백업 확인: 만약 문제가 발생할 경우를 대비해 기존 데이터와 구조를 백업했는지 확인합니다.
  • 삭제 시점: 운영 환경에서는 작업 완료 후 충분히 안정성을 확인한 뒤 삭제하는 것이 좋습니다.
  • 오류 시 롤백 계획: DBMS_REDEFINITION 패키지를 사용할 경우, 오류가 발생하면 작업을 롤백하여 기존 테이블로 복원할 수 있습니다.

4. 중간 테이블을 보존해야 하는 경우

  • 테스트용으로 유지: 새로운 테이블 구조에 대한 테스트를 더 진행해야 하는 경우.
  • 기록 목적: 일시적으로 데이터 이전 과정을 검토하기 위해 중간 테이블을 보관할 수도 있습니다.

결론적으로, 작업이 성공적으로 완료되었다면 중간 테이블을 삭제하는 것이 일반적이며, 이는 디스크 공간 확보와 관리 용이성 측면에서 유리합니다. 추가 질문이 있으시면 말씀해 주세요! 😊

 

오라클에서 Range Partitioning 을 날짜별로 한다고 하면 시간이 지날수록 자동으로 주기적 스케쥴로 파티션을 재설정하는 예

오라클에서 Range Partitioning을 날짜별로 구성하고, 시간이 지남에 따라 자동으로 새로운 파티션을 생성하는 방법을 설정하는 예를 설명드리겠습니다. 이 작업은 주로 Interval Partitioning을 사용하거나 PL/SQL 스크립트와 DBMS_SCHEDULER를 활용하여 구현할 수 있습니다.

1. Interval Partitioning 사용

Oracle 11g 이상에서는 Interval Partitioning을 사용하여 자동으로 날짜별 파티션을 추가할 수 있습니다. 아래는 예제입니다.

CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    amount NUMBER
)
PARTITION BY RANGE (sale_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) -- 매월 새로운 파티션 생성
(
    PARTITION p_initial VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
);

설명:

  • INTERVAL 키워드를 사용하여 특정 주기(예: 1개월)마다 파티션이 자동 생성됩니다.
  • 초기 파티션 p_initial은 2024-01-01 이전 데이터를 처리합니다.
  • 이후 데이터는 주어진 날짜 간격(NUMTOYMINTERVAL)에 따라 새로운 파티션에 자동 삽입됩니다.

2. DBMS_SCHEDULER와 PL/SQL로 스크립트 실행

Interval Partitioning을 사용할 수 없는 경우, 주기적으로 새로운 파티션을 생성하는 스케줄러 작업을 설정해야 합니다.

2.1 파티션 추가 스크립트

BEGIN
    FOR i IN 1..12 LOOP
        EXECUTE IMMEDIATE '
            ALTER TABLE sales ADD PARTITION p_' || TO_CHAR(ADD_MONTHS(SYSDATE, i), 'YYYYMM') || '
            VALUES LESS THAN (TO_DATE(''' || TO_CHAR(ADD_MONTHS(SYSDATE, i + 1), 'YYYY-MM-DD') || ''', ''YYYY-MM-DD''))';
    END LOOP;
END;
/

설명:

  • ALTER TABLE 명령어를 사용하여 미래의 12개월 동안 사용할 파티션을 미리 생성합니다.
  • TO_CHAR와 ADD_MONTHS를 활용하여 파티션 이름과 경계값을 동적으로 생성합니다.

2.2 스케줄러 작업 생성

BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'add_monthly_partition',
        job_type        => 'PLSQL_BLOCK',
        job_action      => '
            BEGIN
                FOR i IN 1..1 LOOP
                    EXECUTE IMMEDIATE ''ALTER TABLE sales ADD PARTITION p_'' || 
                        TO_CHAR(ADD_MONTHS(SYSDATE, i), ''YYYYMM'') || 
                        '' VALUES LESS THAN (TO_DATE('''' || TO_CHAR(ADD_MONTHS(SYSDATE, i + 1), ''YYYY-MM-DD'') || '''', ''''YYYY-MM-DD''''))'';
                END LOOP;
            END;',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=MONTHLY;BYMONTHDAY=1',
        enabled         => TRUE
    );
END;
/

설명:

  • DBMS_SCHEDULER.CREATE_JOB를 사용하여 매월 1일마다 새로운 파티션을 생성하도록 작업을 생성합니다.
  • repeat_interval에서 FREQ=MONTHLY;BYMONTHDAY=1은 매월 첫 번째 날에 작업이 실행되도록 설정합니다.

3. 결론

  • Interval Partitioning은 자동으로 파티션을 생성하기에 간단하고 유지보수가 적습니다.
  • 스케줄러 작업과 PL/SQL 스크립트는 유연성을 제공합니다. 특히, 복잡한 파티션 논리가 필요한 경우 유용합니다.

필요한 부분에 따라 적용 방법을 선택하시고, 추가적인 질문이 있으시면 언제든 말씀해 주세요. 😊

728x90
728x90

'Oracle Database 강좌' 카테고리의 다른 글

오라클 데이터베이스와 웹훅(Webhook) 통합: PL/SQL과 JSON_OBJECT_T를 활용한 실전 가이드  (1) 2024.12.16
오라클 스토어드 프로시저에서 JSON 데이터 생성 및 출력: 심층 분석  (0) 2024.12.16
오라클 페이지네이션 성능 최적화: ROWNUM vs ROW_NUMBER() 심층 분석 및 추가 최적화 방안  (1) 2024.12.14
오라클 페이징 구현 및 스토어드 프로시저 활용: 심층 분석 및 실제 예제  (2) 2024.12.14
오라클 스토어드 프로시저 : 실무에서 활용하는 다양한 기능  (2) 2024.12.08

'Oracle Database 강좌'의 다른글

  • 현재글오라클 대용량 테이블 성능 향상: 테이블 파티셔닝 재정의를 통한 최적화

관련글

티스토리툴바