서술형문제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자격검정 실전문제