#2 A등급 고객들이 가장 마지막 결제일에 결제한 물품 개수 및 이름
A등급 고객은 고객 전체의 2%
평균적으로 주문하면 91%정도 결제를 함
==================================================
기본적으로 A등급 고객이 고객전체의 2%기 때문에
주문 테이블에 고객번호, 주문일자 인덱스를 구성해주고
push_predicate와 nl조인를 통해 2%해당하는 고객들의 마지막 주문일자를 구하고 테이블 랜덤액세스를 통해 상품 번호를 가져오는게 좋다고 판단함. 그리고 결제 여부만 판단하면 되기때문에 EXSITS를 인덱스를 통해 1건만 찾아내면 되므로 인덱스 구성을 생각했다.
그렇기에 아래와 같은 개의 인덱스를 구성했다.
CREATE INDEX ORDER_IDX_01 ON 주문(고객번호,주문일자);
CREATE INDEX PAY_IDX_01 ON 결제(주문번호);그리고 마지막 결제일은 다음의 SQL을 통해 가져오는 것을 구상했었다.
select *
(
select 고객번호, 주문번호, 상품번호,주문일자, max(주문일자) over(partition by 고객번호) max
from 주문 o3
) t
where t.max = t.주문일자select *
from
(
select 고객번호, 주문번호, 상품번호,주문일자, rank() over(partition by 고객번호 order by 주문일자 desc) rnk
from 주문 o3
) t
where t.rnk =1rank와 max를 생각 했었다.
그래서 초안은 아래와 같이 나왔었다.
하지만 실행계획이 원하는 대로 되지않아 많은 시간을 잡아먹었고 코스트 자체도 72만에 가까운 코스트가 나왔다. 2%에 해당하는 작은 데이터임에도 불구하고 FAST FULL SCAN으로 결제 테이블과 주문 테이블을 전체 다 읽는게 제일 큰 문제고 테이블의 순서 자체도 고객을 먼저 읽고 주문을 읽는 것이 되지 않았다. 힌트를 통해 제어를 해주려고 해도 원하는 대로 힌트가 먹히지 않았다. UNUSED, UNRESOLVED가 뜨면서 두개 다 안되는 것을 경험하였다. EXISTS를 밖으로 빼고 안으로 뺌에 따라 코스트가 59~71만정도로 줄여지는 상황이 있었지만 근본적으로 코스트를 많이 줄이는 상황은 아니기에 패스한다.
윈도우 함수가 테이블을 2번 읽는 비효율을 없앨 수 있다고 생각했는데 테이블 전체를 읽는 비효율을 발생시켜서 다음과 같은 SQL로 상품 테이블은 조인안한채로 실행계획이 어떻게 나오는지 실험하였다.
실행 계획을 보면 알 수 있듯이 MERGE JOIN이 사라지고
처음에 원했던 VIEW PUSHED PREDICATE 실행계획이 나오게 되었다. 코스트도 59~71만이였지만 8만까지 줄임으로서 잘 줄여졌다. 이대로 상품 테이블과 조인하여 결과를 내면 된다.
상품과 조인을 하기전에 SQL을 실행시켜서 실행계획 테스트 해볼때는 merge join이 없었는데 merge join이 생겼다. 그리고 코스트도 24만으로 급성장하였다. 아마도 인라인 VIEW를 머지하여 새로운 실행계획을 만들어서 하는 바람에 실행계획이 꼬인것 같아서
/*+no_merge*/ 힌트를 추가하여서 마무리하였다.
최종 SQL
select 상품명 , cnt
from (
select /*+NO_MERGE*/상품번호, count(상품번호) cnt
from
(
select 고객번호, max(주문일자) max
from 주문 o3
group by 고객번호
) t, 주문 o, 고객 c
where 1=1
and c.고객등급 = 'A'
AND C.고객번호 = T.고객번호
and o.고객번호 = t.고객번호
and o.주문일자 = t.max
and exists(select 1
from 결제 p
where p.주문번호 = o.주문번호)
group by 상품번호
) t
, 상품 b
where t.상품번호 = b.상품번호
order by cnt desc
;
