파티션 인덱스 튜닝

파티셔닝 튜닝

개발자를 위한 오라클 SQL 튜닝 내용에서 참고한 내용입니다.

파티션 프루닝 튜닝

파티션 프루닝

• 파티션 프루닝(Partition Pruning)이란 파티셔닝 컬럼이 조건절에 조건으로 들어왔을 때 해당 파티션만 읽고 대상이 아닌 파티션을 스캔하지 않는 기법을 말한다.

• 예를 들어

  • 거래 일자 기준으로 파티셔닝된 대용량 테이블이 있다고 가정한다.

  • 조건절에 거래일자가 없거나 묵시적 형 변환이 발생하면 테이블 내에 모든 파티션을 스캔해야 한다.(PARTITION RANGE ALL)

  • 즉, 파티션 프루닝이 되지 않습니다.

  • 조건절에 거래일자가 있으면 해당 파티션만 읽고 스캔을 멈추게 된다.(PARTITION RANGE ITERATOR)

  • 즉, 파티션 프루닝이 동작하게 된다.

파티션 프루닝이 동작하도록 조건절 튜닝하기

• 테이블 생성

CREATE TABLE TB_TRD
(
    TRD_NO VARCHAR2 (15),  -- 거래번호
    TRD_DT VARCHAR2 (8),   -- 거래일자
    TRD_AMT NUMBER (15),   -- 거래금액
    CUST_ID VARCHAR2 (10), -- 고객ID
    PRDT_CD VARCHAR2 (6),  -- 상품코드
    INST_ID VARCHAR2 (50), -- 입력자ID
    INST_DTM DATE          -- 입력일시
)
    PARTITION BY RANGE (TRD_DT)
        (
        PARTITION P_TB_TRD_201409 VALUES LESS THAN ('20141001'),
        PARTITION P_TB_TRD_201410 VALUES LESS THAN ('20141101'),
        PARTITION P_TB_TRD_201411 VALUES LESS THAN ('20141201'),
        PARTITION P_TB_TRD_201412 VALUES LESS THAN ('20150101'),
        PARTITION P_TB_TRD_201501 VALUES LESS THAN ('20150201'),
        PARTITION P_TB_TRD_201502 VALUES LESS THAN ('20150301'),
        PARTITION P_TB_TRD_201503 VALUES LESS THAN ('20150401'),
        PARTITION P_TB_TRD_201504 VALUES LESS THAN ('20150501'),
        PARTITION P_TB_TRD_201505 VALUES LESS THAN ('20150601'),
        PARTITION P_TB_TRD_201506 VALUES LESS THAN ('20150701'),
        PARTITION P_TB_TRD_201507 VALUES LESS THAN ('20150801'),
        PARTITION P_TB_TRD_201508 VALUES LESS THAN ('20150901'),
        PARTITION P_TB_TRD_201509 VALUES LESS THAN ('20151001'),
        PARTITION P_TB_TRD_MAX VALUES LESS THAN (MAXVALUE)
        );

CREATE TABLE DUAL_10
(
    DUMMY VARCHAR2 (1)
);

• 더미 데이터 생성

INSERT INTO DUAL_10
SELECT 'X'
FROM DUAL CONNECT BY LEVEL <= 10;

COMMIT;

ALTER TABLE TB_TRD NOLOGGING;

INSERT /*+ APPEND */ INTO TB_TRD -- APPEND 힌트 사용
SELECT LPAD(TO_CHAR(ROWNUM), 15, '0'),
       TO_CHAR(SYSDATE - TRUNC(DBMS_RANDOM.VALUE(1, 365)), 'YYYYMMDD'),
       TRUNC(DBMS_RANDOM.VALUE(1000, 100000)),
       LPAD(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(0, 100000))), 10, '0'),
       LPAD(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(0, 10000))), 6, '0'),
       'DBMSEXPERT',
       SYSDATE
FROM DUAL_10,
     (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 1000000);

COMMIT;

• 제약조건 생성

ALTER TABLE TB_TRD
    ADD CONSTRAINT TB_TRD_PK
        PRIMARY KEY (TRD_NO);

• 통계정보 생성

ANALYZE TABLE TB_TRD COMPUTE STATISTICS
FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 254;

파티션 프루닝 튜닝 전

SELECT *
FROM TB_TRD
WHERE TRD_DT
          BETWEEN TO_DATE(TO_CHAR(SYSDATE - 180, 'YYYYMMDD'), 'YYYYMMDD')
          AND TO_DATE(TO_CHAR(SYSDATE - 120, 'YYYYMMDD'), 'YYYYMMDD');

• SQL 분석

  • 현재 시점으로부터 180일 전부터 120일 전까지의 YYYYMMDD를 TO_DATE 함수를 이용하여 DATE 타입으로 형 변환하여 조건을 주었다.

• SQL 문제점

  • TRD_DT 컬럼의 데이터 타입은 VARCHAR2인데, TO_DATE 함수를 사용함으로써 VARCHAR2와 DATE의 비교 연산이 되었다.

  • 오라클은 이럴 경우 DATE 타입에 우선권을 주게 되므로 TRD_DT 컬럼을 DATE 타입으로 묵시적 형 변환을 하게 된다.

  • 묵시적 형 변환으로 인해 파티션 프루닝이 되지 않고, 불필요한 파티션까지 모두 스캔하게 되므로 성능이 안 좋은 SQL이다.

파티션 프루닝 튜닝 후

SELECT *
FROM TB_TRD
WHERE TRD_DT
          BETWEEN TO_CHAR(SYSDATE - 180, 'YYYYMMDD')
          AND TO_CHAR(SYSDATE - 120, 'YYYYMMDD');

• SQL 분석

  • TRD_DT 컬럼의 데이터 타입은 VARCHAR2이므로 문자열 상수로 범위 조건을 주엇다.

  • 같은 데이터 타입 간의 비교 연산이 이루어지므로 묵시적 형 변환이 이루어지지 않게 되었으며 파티션 프루닝이 가능해졌다.