쿼리 튜닝 실습(partition, pruning)

※ 문제

/*

테이블

CONSULT_NO NUMBER -- 상담번호

CONSULTANT_ID VARCHAR2(4) -- 상담자 아이디

con_dt VARCHAR2(8) -- 상담일자

CON_TM VARCHAR2(4) -- 상담시간

RSLT_CD VARCHAR2(4) -- 상담처리 결과 0800(진행), 0900(완료)

AFTRSLT VARCHAR2(2) -- 사후처리 결과 11(보류), 21(부서이관)

CUST_ID -- 고객ID

 

1) 아래 요건에 맞는 쿼리를 작성하세요.

- 조건 : 상담자 아이디가 바인드변수로 제공

- 상담자가 당월 1일 00시부터 현재일자 12시까지 상담한 정보를 조회하고자 한다.

예) 오늘이 9월22일 일경우 => 2014.09.01 00:00 ~ 2014.09.22 12:00

- 조회 정보

- 해당 상담자가 상담한 건수,

- 상담처리 결과가 완료된 건수,

- 사후처리가 부서이관된 건수(상담처리 결과는 완료된 건)

- Unique한 상담 고객 수

2) 인덱스를 구성하세요.

상담 테이블에서 상담일자로 월별 파티션이 되어 있다. 위 쿼리를 기준으로 가장

최적화된 인덱스를 구성하세요.

단, 테이블은 60개월분만 유지한다. 즉, 매달 1일 60개월 이전 데이터는 빠르게

삭제 되어야 한다.

 

- 인덱스 구성 칼럼?

- 파티션 KEY 칼럼?

- LOCAL / GLOBAL 인덱스 선택

- GLOBAL 인데스 일 경우 파티션 키 기준 (년, 월, 일 기타 등등)

*/

파티션 테이블 조회의 핵심은 Where 조건절에 파티션 컬럼을 추가하여 Partition Pruning 효과를 활용하는 방법입니다. 파티션 프루닝(Partition Pruning)이란 파티셔닝 컬럼이 조건절에 조건으로 들어왔을 때 해당 파티션만 읽고 대상이 아닌 파티션을 스캔하지 않는 기법을 말합니다. 단, 파티셔닝 컬럼을 사용할 때 묵시적 형변환이 발생하지 않도록 주의해야 합니다. 

 

※ 정답 

 

CREATE INDEX DEV.IX_T_CONSULT55_01 ON DEV.T_CONSULT55(CONSULTANT_ID, CON_DT, CON_TM) LOCAL;

-- 1번

SELECT /*+ INDEX (A IX_T_CONSULT55_01) */

:CONSULTANT_ID CONSULTANT_ID

, COUNT(*) 상담건수

, NVL(SUM(CASE WHEN RSLT_CD = '0900' THEN 1 END), 0) 상담완료건수

, NVL(SUM(CASE WHEN RSLT_CD = '0900' AND AFTRSLT_CD = '21' THEN 1 END), 0) a부서이관건수

, COUNT(DISTINCT CUST_ID) 상담고객수

FROM T_CONSULT55 A

WHERE CONSULTANT_ID = :CONSULTANT_ID

AND CON_DT BETWEEN TO_CHAR(SYSDATE, 'YYYYMM') || '01' AND TO_CHAR(SYSDATE, 'YYYYMMDD')

AND CON_DT || CON_TM <= TO_CHAR(SYSDATE, 'YYYYMMDD') || '1200'

;

조건절에 사용하는 컬럼을 기준으로 인덱스를 생성해주었습니다. 또한, 힌트를 통해 해당 인덱스가 수행되도록 선언하였습니다. 

CON_DT 조건절의 묵시적 형변환을 방지하기 위해 문자열 형대로 변형해주었습니다. 

NVL(SUM... 을 보면 '0900' 인 경우에 1로 합계를 구해줍니다. 여기에 ELSE로 붙여 0 처리하지 않는 이유는 SUM 연산의 효율을 높이기 위함입니다. null인 경우 SUM이 수행되지 않습니다. 그렇기 때문에 굳이 ELSE, 0 처리를 하여 쿼리를 작성할 필요가 없습니다.

SELECT의 첫 번째 컬럼으로 :CONSULTANT_ID 파라미터를 활용했으며, 테이블의 컬럼을 활용할 경우 A.CONSULTANT_ID로 선언해주고 이를 PGA영역에 올리는 자원 소모가 발생하여, 파라미터를 활용한 이유입니다.

 

※ 문제 실습을 위한 테이블 생성, 데이터 입력, 세션 설정 변경

202001 - 202501 까지 월별 파티션을 생성하였습니다. CON_DT 컬럼을 기준으로 파티션이 생성되었고 CON_DT를 기준으로 데이터를 다루기(삭제,업데이터,조회 등) 유리해집니다. 파티션은 큰 테이블을 관리하는 데 용이하지만, 파티션 생성 등 관리 포인트가 발생합니다. 파티션을 생성하지 않으면, 특정 설정에 따라 임의의 공간에 데이터를 저장할 수 있게 설정할 수 있지만, 주기적으로 확인이 필요합니다. 또한, 작은 테이블에 억지로 파티션을 만들게 되면 오히려 성능에 좋지 못합니다. 파티션이 반드시 필요하다 판단이 되면 테이블 생성과 동시에 반드시 파티션 생성을 해주어야 합니다.

ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;

ALTER SESSION SET SORT_AREA_SIZE=2000000000;

ALTER SESSION SET HASH_AREA_SIZE=2000000000;

DROP TABLE DEV.T_CONSULT55;

 

CREATE TABLE DEV.T_CONSULT55

(CONSULT_NO NUMBER, -- 상담번호

CONSULTANT_ID VARCHAR2(4), -- 상담자_ID

CON_DT VARCHAR2(8), -- 상담일자

CON_TM VARCHAR2(4), -- 상담시간

RSLT_CD VARCHAR2(4), -- 0800(진행), 0900(완료)

AFTRSLT_CD VARCHAR2(2), -- 11보류, 21부서이관

CUST_ID VARCHAR2(8), -- 고객_ID

CONSTRAINT PK_T_CONSULT55 PRIMARY KEY(CONSULT_NO, CON_DT)

)

PARTITION BY RANGE (CON_DT)

(

PARTITION PT_201912 VALUES LESS THAN ('202001'),

PARTITION PT_202001 VALUES LESS THAN ('202002'),

PARTITION PT_202002 VALUES LESS THAN ('202003'),

PARTITION PT_202003 VALUES LESS THAN ('202004'),

PARTITION PT_202004 VALUES LESS THAN ('202005'),

PARTITION PT_202005 VALUES LESS THAN ('202006'),

PARTITION PT_202006 VALUES LESS THAN ('202007'),

PARTITION PT_202007 VALUES LESS THAN ('202008'),

PARTITION PT_202008 VALUES LESS THAN ('202009'),

PARTITION PT_202009 VALUES LESS THAN ('202010'),

PARTITION PT_202010 VALUES LESS THAN ('202011'),

PARTITION PT_202011 VALUES LESS THAN ('202012'),

PARTITION PT_202012 VALUES LESS THAN ('202101'),

PARTITION PT_202101 VALUES LESS THAN ('202102'),

PARTITION PT_202102 VALUES LESS THAN ('202103'),

PARTITION PT_202103 VALUES LESS THAN ('202104'),

PARTITION PT_202104 VALUES LESS THAN ('202105'),

PARTITION PT_202105 VALUES LESS THAN ('202106'),

PARTITION PT_202106 VALUES LESS THAN ('202107'),

PARTITION PT_202107 VALUES LESS THAN ('202108'),

PARTITION PT_202108 VALUES LESS THAN ('202109'),

PARTITION PT_202109 VALUES LESS THAN ('202110'),

PARTITION PT_202110 VALUES LESS THAN ('202111'),

PARTITION PT_202111 VALUES LESS THAN ('202112'),

PARTITION PT_202112 VALUES LESS THAN ('202201'),

PARTITION PT_202201 VALUES LESS THAN ('202202'),

PARTITION PT_202202 VALUES LESS THAN ('202203'),

PARTITION PT_202203 VALUES LESS THAN ('202204'),

PARTITION PT_202204 VALUES LESS THAN ('202205'),

PARTITION PT_202205 VALUES LESS THAN ('202206'),

PARTITION PT_202206 VALUES LESS THAN ('202207'),

PARTITION PT_202207 VALUES LESS THAN ('202208'),

PARTITION PT_202208 VALUES LESS THAN ('202209'),

PARTITION PT_202209 VALUES LESS THAN ('202210'),

PARTITION PT_202210 VALUES LESS THAN ('202211'),

PARTITION PT_202211 VALUES LESS THAN ('202212'),

PARTITION PT_202212 VALUES LESS THAN ('202301'),

PARTITION PT_202301 VALUES LESS THAN ('202302'),

PARTITION PT_202302 VALUES LESS THAN ('202303'),

PARTITION PT_202303 VALUES LESS THAN ('202304'),

PARTITION PT_202304 VALUES LESS THAN ('202305'),

PARTITION PT_202305 VALUES LESS THAN ('202306'),

PARTITION PT_202306 VALUES LESS THAN ('202307'),

PARTITION PT_202307 VALUES LESS THAN ('202308'),

PARTITION PT_202308 VALUES LESS THAN ('202309'),

PARTITION PT_202309 VALUES LESS THAN ('202310'),

PARTITION PT_202310 VALUES LESS THAN ('202311'),

PARTITION PT_202311 VALUES LESS THAN ('202312'),

PARTITION PT_202312 VALUES LESS THAN ('202401'),

PARTITION PT_202401 VALUES LESS THAN ('202402'),

PARTITION PT_202402 VALUES LESS THAN ('202403'),

PARTITION PT_202403 VALUES LESS THAN ('202404'),

PARTITION PT_202404 VALUES LESS THAN ('202405'),

PARTITION PT_202405 VALUES LESS THAN ('202406'),

PARTITION PT_202406 VALUES LESS THAN ('202407'),

PARTITION PT_202407 VALUES LESS THAN ('202408'),

PARTITION PT_202408 VALUES LESS THAN ('202409'),

PARTITION PT_202409 VALUES LESS THAN ('202410'),

PARTITION PT_202410 VALUES LESS THAN ('202411'),

PARTITION PT_202411 VALUES LESS THAN ('202412'),

PARTITION PT_202412 VALUES LESS THAN ('202501')

)

NOLOGGING;

CREATE OR REPLACE PUBLIC SYNONYM T_CONSULT55 FOR DEV.T_CONSULT55;

INSERT /*+ APPEND */ INTO T_CONSULT55

SELECT ROWNUM CONSULT_NO ,

'T'|| LPAD(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(1, 300))), 3, '0') CONSULTANT_ID,

CON_DT,

LPAD(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(9, 18))), 2, '0')

|| LPAD(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(0, 59))), 2, '0') CON_TM ,

CASE WHEN MOD(ROWNUM, 100) = 0 THEN '0800' ELSE '0900' END RSLT_CD ,

CASE WHEN MOD(ROWNUM, 100) BETWEEN 90 AND 99 THEN '11'

WHEN MOD(ROWNUM, 100) BETWEEN 80 AND 89 THEN '21'

ELSE '00'

END AFTRSLT_CD ,

'C'|| LPAD(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(1, 6000000))), 7, '0') CUST_ID

FROM (SELECT TO_CHAR(TO_DATE('20200101', 'YYYYMMDD') + ROWNUM -1, 'YYYYMMDD') CON_DT

FROM DUAL CONNECT BY LEVEL <= 1800),

(SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=10000)

--ORDER BY DBMS_RANDOM.RANDOM()

;

COMMIT;

EXECUTE DBMS_STATS.GATHER_TABLE_STATS('DEV', 'T_CONSULT55');

ALTER SESSION SET WORKAREA_SIZE_POLICY=AUTO;