서술형문제2 TRACE 결과 분석
아래 SQL과 트레이스 결과를 분석해서 개선된 SQL을 작성하시오
1) 원하는 실행계획이 정확히 나오도록 힌트도 함께 기술할 것.
2) 최적의 인덱스 구성방안도 함께 제시할 것
(성능 개선에 도움이 되지 않는 필요 이상의 컬럼을 추가하는 것은 감점요인)
[SQL문]
select o.주문일시, o.주문번호, c.고객번호, c.고객명, c.연락처, o.주문금액, o.배송지
from 고객 c,주문 o
where o.주문일시 between to_date('20150301','yyyymmdd') and to_date('20150314235959','yyyymmddhh24miss')
and o.고객번호 = c.고객번호
and c.거주지역코드||c.고객명 in ('02김철수','05홍길동')
order by o.주문일시, c.고객명[SQL TRACE]
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.002 0 0 0 0
Execute 1 0.00 0.000 0 0 0 0
Fetch 2 0.828 7.136 65296 114341 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.828 7.138 65296 114341 0 5
Rows Row Source Operation
------- ---------------------------------------------------
5 SORT ORDER BY (cr=114341 pr=65296 pw=0 time=0 us cost=21342)
5 HASH JOIN (cr=114341 pr=65296 pw=0 time=28 us cost=21127)
20 TABLE ACCESS FULL 고객 (cr=76929 pr=26924 pw=0 time=38 us cost=21019)
45185 PARTITION RANGE SINGLE PARTITION: 1 1 (cr=37412 pr=28372 pw=0 time=33696 us cost=107)
45185 TABLE ACCESS FULL 주문 PARTITION: 1 1 (cr=37412 pr=28372 pw=0 time=22209 us cost=107)
[인덱스 구성]
고객 테이블 : 고객_PK(고객번호)
주문 테이블 : 주문_PK(주문번호)
-----------------------------------------------------------------------------------------------------------
[튜닝 Point]
SQL TRACE를 확인하여 얻을 수 있는 정보를 우선 정리하는 것이 중요한 것같습니다.
제일 먼제 눈에 띄는 것은 고객 테이블과 주문 테이블이 모두 TABLE FULL SCAN으로 접근한다는 것입니다. 이것을 INDEX RANGE SCAN으로 바꿔준다면 성능 향상으로 이어질 수 있겠습니다.
두번째로는 JOIN 방식입니다. 현재에는 HASH JOIN으로 두 테이블을 조인하고 있습니다.
HASH JOIN은 대용량 테이블일 경우(야간 배치일 경우) 사용하는 JOIN입니다.
해당 SQL은 20건의 고객테이블과 45185건의 주문테이블을 조인해서 5건의 결과값을 얻는 SQL이기 때문에, HASH JOIN 보다는 NL 조인이 더 나아 보입니다. 건수가 작은 고객테이블을 드라이빙테이블로 하여 주문테이블을 조인하는 방식을 진행하면 될것 같습니다.
세번째는 조건절에 맞는 인덱스 생성입니다. 현재는 두 테이블 다 PK인덱스 하나씩 밖에 없습니다. 따라서 조건절에서 인덱스를 타기 위해서는 각 테이블 별로 인덱스를 추가해줘야 합니다.
고객 테이블의 경우, 조건절에 고객번호,거주지역코드,고객명이 있습니다. 고객번호는 PK가 존재하므로, (거주지역코드,고객명) 인덱스로 생성하면 될 것같습니다.
주문 테이블의 경우, 조건절에 고객번호, 주문일자가 있습니다. 범위조건인 주문일자가 선두컬럼으로 오는거보다 고객번호를 선두컬럼으로 하여 (고객번호,주문일자)순으로 인덱스를 생성하면 될것 같습니다. 또 주문 테이블은 파티션테이블입니다. 따라서 인덱스 생성시 일반인덱스와 명령어가 조금 다른점도 생각해야 할 것 같네요.
마지막으로 SQL 중 and c.거주지역코드||c.고객명 in ('02김철수','05홍길동') 이부분을 수정해야 할 것같은데요, 기존에 이 문장은 컬럼을 수정해서 인덱스가 있더라도 탈 수 없는 상태입니다. 따라서 SQL을 수정하여 인덱스를 탈 수 있게 바꿔줘야 할 것입니다.
위의 Point를 참고하여 정답을 확인하겠습니다.
인덱스 생성
CREATE INDEX 고객_IDX1 on 고객(거주지역코드,고객명);
CREATE INDEX 주문_IDX1 on 주문(고객번호,주문일시) LOCAL;
SQL 1번
select /*+ LEADING(C) USE_NL(O) INDEX(C 고객_IDX1) INDEX(O 주문_IDX1) */ o.주문일시, o.주문번호, c.고객번호, c.고객명, c.연락처, o.주문금액, o.배송지
from 고객 c,주문 o
where o.주문일시 between to_date('20150301','yyyymmdd') and to_date('20150314235959','yyyymmddhh24miss')
and o.고객번호 = c.고객번호
and (c.거주지역코드,c.고객명) in (('02','김철수'),('05','홍길동'))
order by o.주문일시, c.고객명
SQL 2번
select /*+ LEADING(C) USE_NL(O) INDEX(C 고객_IDX1) INDEX(O 주문_IDX1) */ o.주문일시, o.주문번호, c.고객번호, c.고객명, c.연락처, o.주문금액, o.배송지
from 고객 c,주문 o
where o.주문일시 between to_date('20150301','yyyymmdd') and to_date('20150314235959','yyyymmddhh24miss')
and o.고객번호 = c.고객번호
and (c.거주지역코드 = '02' and c.고객명 = '김철수')
or (c.거주지역코드 = '05' and c.고객명 = '홍길동')
order by o.주문일시, c.고객명
출처 : SQL자격검정 실전문제