[Oracle] 대용량 데이터베이스 성능 향상: Oracle Partitioning 실전 사용 전략

Oracle Partitioning은 대용량 데이터베이스의 성능을 개선하는 데 중요한 역할을 합니다.
다양한 파티셔닝 전략을 활용하여 데이터를 효율적으로 관리하고, 쿼리 성능을 최적화할 수 있습니다.

 

1. Oracle Partitioning이란 무엇인가?

Oracle Partitioning은 하나의 테이블이나 인덱스를 여러 개의 파티션으로 나누어 관리하는 기능입니다. 각 파티션은 물리적으로 별개의 저장 공간에 저장되지만, 논리적으로는 하나의 테이블처럼 취급됩니다. 이 기능은 대용량 데이터베이스에서 쿼리 성능을 향상시키고, 데이터 관리를 효율적으로 할 수 있게 해줍니다.

주요 이점은 다음과 같습니다:

• 쿼리 성능 향상: 자주 조회되는 데이터나, 특정 범위에 있는 데이터에 대해서만 쿼리 성능을 집중할 수 있습니다.
• 데이터 관리 용이: 데이터가 분할되어 있기 때문에, 특정 파티션에 대해서만 백업하거나 삭제할 수 있어 관리가 용이합니다.
• 병렬 처리: 파티션마다 독립적인 처리가 가능하여, 병렬 쿼리 처리로 성능이 향상됩니다.

 

2. Oracle Partitioning 종류

Oracle은 다양한 파티셔닝 방식을 지원합니다. 각 방식은 데이터의 특성에 맞춰 최적의 성능을 발휘할 수 있도록 돕습니다. 주요 파티셔닝 종류는 다음과 같습니다.

(1) Range Partitioning

Range Partitioning은 데이터를 값의 범위에 따라 나누는 방식입니다. 예를 들어, 날짜 데이터를 기준으로 연도별, 월별, 일별로 데이터를 나눌 수 있습니다. 주로 시간 기반 데이터에 적합합니다.

예시:

• 2020년, 2021년, 2022년의 데이터를 각각 하나의 파티션으로 나누어 관리.

CREATE TABLE sales (
    sale_id INT,
    sale_date DATE,
    amount NUMBER
)
PARTITION BY RANGE (sale_date) (
    PARTITION p2020 VALUES LESS THAN (TO_DATE('01-JAN-2021','DD-MON-YYYY')),
    PARTITION p2021 VALUES LESS THAN (TO_DATE('01-JAN-2022','DD-MON-YYYY')),
    PARTITION p2022 VALUES LESS THAN (TO_DATE('01-JAN-2023','DD-MON-YYYY'))
);

 

(2) List Partitioning

List Partitioning은 데이터를 명시적 값에 따라 분할하는 방식입니다. 특정한 고정된 값들에 따라 데이터를 분할하고, 주로 카테고리별 데이터에 유용합니다.

예시:

• 지역별로 데이터를 나누거나, 특정 카테고리 값으로 나누는 경우.

CREATE TABLE sales (
    sale_id INT,
    region VARCHAR2(50),
    amount NUMBER
)
PARTITION BY LIST (region) (
    PARTITION pUS VALUES ('US'),
    PARTITION pEU VALUES ('EU'),
    PARTITION pAS VALUES ('AS')
);

 

(3) Hash Partitioning

Hash Partitioning은 데이터를 해시 함수에 의해 무작위로 분할하는 방식입니다. 데이터가 고르게 분포되도록 하여, 균등한 파티셔닝을 보장합니다. 데이터의 분포가 고르지 않거나 예측할 수 없는 경우에 유용합니다.

예시:

• 대규모 사용자 데이터를 분할하여 관리할 때.

CREATE TABLE users (
    user_id INT,
    name VARCHAR2(100),
    region VARCHAR2(50)
)
PARTITION BY HASH (user_id) 
PARTITIONS 4;​

(4) Composite Partitioning

Composite Partitioning은 두 가지 이상의 파티셔닝 방식을 결합하는 것입니다. Range Partitioning과 Hash Partitioning을 결합하여 성능과 관리의 이점을 동시에 누릴 수 있습니다.

예시:

• 먼저 날짜 범위로 파티션을 나누고, 각 범위 내에서 해시 방식으로 데이터를 나누는 경우.

sql

코드 복사

CREATE TABLE orders ( order_id INT, order_date DATE, customer_id INT ) PARTITION BY RANGE (order_date) SUBPARTITION BY HASH (customer_id) PARTITIONS 4 ( PARTITION p2020 VALUES LESS THAN (TO_DATE('01-JAN-2021','DD-MON-YYYY')), PARTITION p2021 VALUES LESS THAN (TO_DATE('01-JAN-2022','DD-MON-YYYY')) );

CREATE TABLE orders (
    order_id INT,
    order_date DATE,
    customer_id INT
)
PARTITION BY RANGE (order_date) 
SUBPARTITION BY HASH (customer_id) 
PARTITIONS 4 (
    PARTITION p2020 VALUES LESS THAN (TO_DATE('01-JAN-2021','DD-MON-YYYY')),
    PARTITION p2021 VALUES LESS THAN (TO_DATE('01-JAN-2022','DD-MON-YYYY'))
);​

 

3. Oracle Partitioning의 장점

(1) 쿼리 성능 향상

대용량 데이터베이스에서 파티션을 활용하면, 쿼리 성능이 크게 향상됩니다. 예를 들어, Range Partitioning을 사용하면, 데이터의 일부 파티션만을 조회하여 검색 속도를 빠르게 할 수 있습니다. 쿼리가 필요한 범위만 검색하면 되기 때문에, 전체 데이터를 조회하는 것보다 훨씬 효율적입니다.

(2) 데이터 관리 용이

데이터 삭제, 업데이트, 백업 등 관리 작업을 파티션 단위로 수행할 수 있습니다. 예를 들어, 1년치 데이터를 삭제해야 할 경우, 해당하는 파티션만 삭제하면 전체 테이블을 대상으로 작업하는 것보다 시간이 훨씬 단축됩니다.

(3) 병렬 처리 지원

각 파티션은 독립적으로 작업할 수 있어, 병렬 쿼리 처리가 가능합니다. 이를 통해 쿼리 응답 시간을 단축시키고, 전체 성능을 향상시킬 수 있습니다.

 

4. Oracle Partitioning 설정 방법

Oracle Partitioning을 설정하는 방법은 CREATE TABLE 문을 통해 지정할 수 있습니다. 이때, 파티션을 어떻게 나눌 것인지를 PARTITION BY 절을 통해 정의합니다. 예를 들어, Range Partitioning을 적용하려면, 테이블 생성 시 날짜 필드를 기준으로 파티션을 나눌 수 있습니다.

CREATE TABLE employee (
    emp_id INT,
    emp_name VARCHAR2(100),
    hire_date DATE,
    department_id INT
)
PARTITION BY RANGE (hire_date) (
    PARTITION p1 VALUES LESS THAN (TO_DATE('01-JAN-2010','DD-MON-YYYY')),
    PARTITION p2 VALUES LESS THAN (TO_DATE('01-JAN-2020','DD-MON-YYYY')),
    PARTITION p3 VALUES LESS THAN (TO_DATE('01-JAN-2025','DD-MON-YYYY'))
);

 

5. Oracle Partitioning Best Practices

(1) 파티션 크기 관리

파티션이 너무 많거나 너무 적으면 성능에 영향을 줄 수 있습니다. 각 파티션의 크기를 적절하게 설정하여 시스템 성능을 최적화하는 것이 중요합니다. 일반적으로 파티션의 크기가 너무 작으면 관리 overhead가 증가하고, 너무 크면 쿼리 성능에 영향을 미칩니다.

(2) 파티션 유지보수

파티션을 추가하거나 삭제할 때는 주기적인 유지보수가 필요합니다. 예를 들어, 데이터가 늘어나면 새로운 파티션을 추가해야 하고, 오래된 데이터는 삭제하거나 압축할 수 있습니다.

(3) 파티션 전략 최적화

테이블을 설계할 때, 데이터를 어떻게 파티션할지 비즈니스 요구사항을 기반으로 결정해야 합니다. 범위 기반, 리스트 기반 등의 파티셔닝 방식 중에서 실제 데이터의 분포와 쿼리 성격에 맞는 전략을 선택하는 것이 중요합니다.

 

6. Oracle Partitioning의 실전 적용 사례

Oracle Partitioning은 다양한 산업 분야에서 효과적으로 활용되고 있습니다. 실제 사례를 통해 Oracle Partitioning이 어떻게 성능 개선을 이끄는지 살펴보겠습니다.

(1) 금융 기관에서의 활용

대부분의 금융 기관은 거래 데이터를 관리하는데 엄청난 양의 데이터를 처리해야 합니다. 예를 들어, 고객 거래 내역을 날짜 또는 고객 지역 기준으로 파티셔닝하면, 특정 기간 또는 특정 지역의 데이터만 효율적으로 조회할 수 있습니다. 특히 Range Partitioning을 활용하여 연도별, 월별로 데이터를 나누고, 자주 사용되는 데이터를 우선적으로 캐싱하여 쿼리 성능을 개선할 수 있습니다.

CREATE TABLE transactions (
    transaction_id INT,
    transaction_date DATE,
    amount NUMBER,
    customer_id INT
)
PARTITION BY RANGE (transaction_date) (
    PARTITION p2020 VALUES LESS THAN (TO_DATE('01-JAN-2021', 'DD-MON-YYYY')),
    PARTITION p2021 VALUES LESS THAN (TO_DATE('01-JAN-2022', 'DD-MON-YYYY'))
);

 

(2) 전자상거래 사이트에서의 활용

전자상거래 사이트에서는 수많은 제품, 고객, 주문 데이터를 처리해야 합니다. List Partitioning을 활용하여 지역별로 고객 데이터를 나누거나, Hash Partitioning을 사용해 고르게 데이터를 분산시키는 방식이 유효합니다. 이를 통해 특정 지역이나 특정 제품군의 데이터를 신속하게 조회할 수 있습니다. 또한, 파티션마다 백업 및 복구 작업을 독립적으로 수행할 수 있어, 시스템 가용성을 높이는 데 유리합니다.

 

CREATE TABLE orders (
    order_id INT,
    order_date DATE,
    customer_id INT,
    region VARCHAR2(50)
)
PARTITION BY LIST (region) (
    PARTITION pNorth VALUES ('North'),
    PARTITION pSouth VALUES ('South'),
    PARTITION pEast VALUES ('East'),
    PARTITION pWest VALUES ('West')
);

 

7. Oracle Partitioning의 성능 최적화 팁

Oracle Partitioning을 사용하면서 성능을 최적화하려면 몇 가지 모범 사례를 따르는 것이 중요합니다.

(1) 데이터 접근 패턴에 맞는 파티셔닝 전략

데이터가 어떻게 사용되는지에 따라 파티셔닝 전략을 선택해야 합니다. 예를 들어, 날짜 데이터를 많이 조회한다면 Range Partitioning을, 특정 값에 따라 자주 조회되는 데이터를 나누려면 List Partitioning을 사용하는 것이 좋습니다. 데이터 접근 패턴에 따라 효율적인 쿼리 성능을 얻을 수 있습니다.

(2) 파티션 병합 및 분할

데이터 크기가 커짐에 따라, 기존 파티션을 분할하거나 병합해야 할 수도 있습니다. 예를 들어, Range Partitioning에서 월별 데이터를 다루고 있는데, 데이터가 많아져 한 달에 많은 양의 데이터가 쌓이면 파티션을 병합하거나 새로운 파티션을 추가해야 할 수도 있습니다. 주기적인 파티션 관리가 필요합니다.

(3) 파티션별 인덱스 관리

파티셔닝을 사용할 때, 각 파티션에 대해 인덱스를 따로 관리할 수 있습니다. 파티션 인덱스를 사용하면 특정 파티션에 대한 쿼리 성능을 크게 향상시킬 수 있습니다. 또한, 파티션 프리픽스 인덱스를 사용하면 효율적인 검색이 가능해집니다.

(4) 파티션 프루닝(Partition Pruning) 활용

Oracle은 파티션 프루닝 기능을 제공하여, 쿼리가 실행될 때 불필요한 파티션을 제외시킬 수 있습니다. 예를 들어, 날짜 범위 쿼리에서 쿼리가 특정 범위의 날짜만 조회한다면, 해당 범위에 해당하는 파티션만 조회하게 됩니다. 이를 통해 쿼리 성능을 향상시킬 수 있습니다.

(5) 파티션 상태 모니터링

주기적으로 파티션 상태를 점검하고 파티션 통계를 업데이트해야 합니다. Oracle은 DBMS_STATS 패키지를 통해 파티션별 통계를 관리하고, 이를 바탕으로 최적화된 실행 계획을 제시합니다. 통계가 오래되면 쿼리 성능이 저하될 수 있으므로, 이를 주기적으로 갱신하는 것이 중요합니다.

EXEC DBMS_STATS.GATHER_TABLE_STATS('schema_name', 'table_name', PARTITION => 'p2021');

 

 

9. Oracle Partitioning을 적용할 때의 고려 사항

(1) 파티셔닝의 비용

파티셔닝을 사용하는 것은 성능 향상에 큰 도움이 되지만, 시스템 관리와 디스크 공간에 추가적인 비용이 발생할 수 있습니다. 데이터가 분할되어 여러 파티션에 저장되기 때문에 I/O 성능에 미치는 영향도 고려해야 합니다. 또한, 파티션을 추가하거나 변경할 때 디스크 공간이 급격히 증가할 수 있으므로, 용량 계획이 필요합니다.

(2) 파티셔닝과 데이터 일관성

파티셔닝을 사용할 때 트랜잭션 일관성을 유지하는 것이 중요합니다. 데이터가 여러 파티션에 분산되기 때문에, ACID 원칙을 준수해야 하며, 다중 파티션에 걸친 트랜잭션 처리 시 성능이 떨어질 수 있습니다. 이 점을 고려하여 파티셔닝 전략을 세워야 합니다.

(3) 파티셔닝 지원 여부 확인

모든 Oracle 버전에서 파티셔닝을 지원하지 않기 때문에, 사용 중인 Oracle 라이선스와 버전에 파티셔닝 기능이 포함되어 있는지 확인해야 합니다. Oracle Standard Edition에서는 일부 파티셔닝 기능을 제공하지 않을 수 있기 때문에, 파티셔닝을 활용하려면 Oracle Enterprise Edition이 필요합니다.