SQLP 52회 -2

52회 실기2

실기2. 튜닝 후 플랜 처럼 나오도록 쿼리 개선 , 테이블 순서는 그대로 기술

튜닝 포인트  4가지 1.  파티션 프루닝 유도하기     문제 : substr(a.주문번호, 1, 6) 때문에 파티션 인식 안됨     해결 : a.주문번호 like '202501%' or a.주문번호 like '202502%' 2. 조인 구문을 ANSI 방식으로 변경     문제 : 구식 조인 구문으로 가독성 및 힌트 적용 어려움     해결 :       from 주문 a join 고객 b on b.고객번호 = a.주문번호        join 주문상세 c on c.주문번호 = a.주문번호         join 상품 d on d.상품번호 = c.상품번호        left join 코드상세 f on f.코드구분 ='A02' and f.코드상세유형 = d.상품유형 3. 조인 순서 최적화      문제 : 주문상세, 상품, 코드상세까지 Full scan 병목      해결: 조인순서 변경 /*+ leading(a) use_hash(c b e f) */  4. 뷰 혹은 CTE로 전처리 분리      문제 : 모든 조인을 한번에 하다 보니 불필요한 조인도 스행      해결 :        with 주문기본 as (         seledt a.주문번호, a.주문일시, b.고객유형, c.상품번호          from 주문 a join 고객 b on b.고객번호 = a.주문번호          join 주문상세 c on c.주문번호 = a.주문번호         where a.주문번호 like '202501%' or a.주문번호 like '202502%' )       select ~~ from 주문기본 jb join 상품 d on ~

주문,고객,주문상세,상품,코드상세,코드상세로 나열되게 조인을 하고

create table 주문 (

주문번호 varchar2(16) not null,

constraint 주문_pk primary key(주문번호)

)

partition by(주문번호)(

partition p202501 values less than (‘202502’)

.....

partition p202612 values less than (‘202701’)

);

​

create table 주문상세 (

주문번호 varchar2(16) not null,

constraint 주문_pk primary key(주문번호)

)

partition by(주문번호)(

partition p202501 values less than (‘202502’)

.....

partition p202612 values less than (‘202701’)

);

​

insert into 주문통계

select

trunc(a.주문일시),b.고객유형,e.코드상세유형명,d.상품유형,f.코드상세유형명,

count(*)

from 주문 a,고객 b,주문상세 c,상품 d,코드상세 e,코드상세 f

and substr(a.주문번호,1,6) in (‘202501’,‘202502’)

and b.고객번호=a.주문번호

and c.주문번호=a.주문번호

and d.상품번호=c.상품번호

and e.코드구분(+)=‘A01’

and e.코드상세유형(+)=b.고객유형

and f.코드구분(+)=‘A02’

and f.코드상세유형(+)=d.상품유형

group by trunc(a.주문일시),b.고객유형,e.코드상세유형명,d.상품유형,f.코드상세유형명

​

​

/* 실행계획 */

insert 주문통계

Load as select

Hash join

Table full scan 코드상세

Hash

Table full scan 코드상세

Hash join

Hash group by

View

Hash join

Table full scan 상품

Hash join

Table full scan 고객

Hash join

Table full scan 주문

Table full scan 주문상세

​

실기2 답?
insert into 주문통계
select /*+ ordered use_hash(b) use_hash(c) use_hash(d) use_hash(e) use_hash(f) full(a) full(b) full(c) full(d) full(e) full(f) swap_join_inputs(b) no_swap_join_inputs(c) swap_join_inputs(d) swap_join_inputs(e) swap_join_inputs(f) */
trunc(a.주문일시),b.고객유형,e.코드상세유형명,d.상품유형,f.코드상세유형명,
count(*)
from 주문 a,고객 b,주문상세 c,상품 d,코드상세 e,코드상세 f
and a.주문번호 >= ‘202501’
and a.주문번호 < '202503'
and b.고객번호=a.주문번호
and c.주문번호=a.주문번호
and d.상품번호=c.상품번호
and e.코드구분(+)=‘A01’
and e.코드상세유형(+)=b.고객유형
and f.코드구분(+)=‘A02’
and f.코드상세유형(+)=d.상품유형
group by trunc(a.주문일시),b.고객유형,e.코드상세유형명,d.상품유형,f.코드상세유형명;

 

실기2 no_swap_join_inputs(c) 잘못썼네요

 

파티션된 테이블 2개, 파티션 키컬럼을 가공해서 처리,  from 주문 a,고객 b,주문상세 c,상품 d,코드상세 e,코드상세 f 튜닝 후 플랜이  Hash right outer join           Table full scan 코드상세            Hash right outer join                 Table full scan 코드상세                  Hash join                      Hash group by                            View                                Hash join                                     Table full scan 상품                                      Hash join                                       Table full scan 고객                                             Hash join                                                 Table full scan 주문                                                 Table full scan 주문상세

a,b,c,d 먼저 group by 처리 후 코드상세 조인하고 leading, swap_join_inputs로 순서,build 집합 조절
파티션 가공처리 제거함. 

++inline view로 묶었음에도 no_merge 처리안해서..어케될지..ㅠ