-- [SQL-8-1-8-a]
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT /*+ Leading(((t1 t2) t3)) NestLoop(t1 t2) NestLoop(t1 t2 t3) */
t3.shop_id ,t3.shop_nm ,t1.join_tp ,t1.mbr_id ,t2.ord_dtm ,t2.ord_amt
FROM startdbpg.ms_mbr_big t1
INNER JOIN startdbpg.tr_ord_big t2 ON (t2.mbr_id = t1.mbr_id)
INNER JOIN startdbpg.ms_shop_big t3 ON (t3.shop_id = t2.shop_id)
WHERE t1.mbr_gd = 'SILV'
AND t1.join_dtm >= '2022-04-20'::date
AND t1.join_dtm < '2022-05-01'::date
AND t2.ord_dtm >= '2023-12-21'::date
AND t2.ord_dtm < '2023-12-26'::date
AND t3.shop_start_ymd LIKE '202001%'
AND t3.table_qty >= 10
ORDER BY ord_amt DESC;
SQL
복사
-- [SQL-8-1-8-b]: [SQL-8-1-8-a]를 테이블별로 분리해서 카운트
SELECT 't1' tab ,COUNT(*) cnt -- 6,121건
FROM startdbpg.ms_mbr_big t1
WHERE t1.mbr_gd = 'SILV'
AND t1.join_dtm >= '2022-04-20'::date
AND t1.join_dtm < '2022-05-01'::date;
SELECT 't2' tab ,COUNT(*) cnt -- 97,344건
FROM startdbpg.tr_ord_big t2
WHERE t2.ord_dtm >= '2023-12-21'::date
AND t2.ord_dtm < '2023-12-26'::date
SELECT 't3' tab ,COUNT(*) cnt -- 15건
FROM startdbpg.ms_shop_big t3
WHERE t3.shop_start_ymd LIKE '202001%'
AND t3.table_qty >= 10;
SQL
복사
-- [SQL-8-1-8-c]: [SQL-8-1-8-a]에서 t2와 t3만 조인해서 카운트
SELECT COUNT(*) cnt
FROM startdbpg.tr_ord_big t2
INNER JOIN startdbpg.ms_shop_big t3 ON (t3.shop_id = t2.shop_id)
WHERE t2.ord_dtm >= '2023-12-21'::date
AND t2.ord_dtm < '2023-12-26'::date
AND t3.shop_start_ymd LIKE '202001%'
AND t3.table_qty >= 10;
SQL
복사
-- [SQL-8-1-8-d]: x24 인덱스 생성 후 [SQL-8-1-8-a]를 힌트를 변경해 재실행
CREATE INDEX tr_ord_big_x24 ON startdbpg.tr_ord_big(shop_id, ord_dtm);
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT /*+ Leading(((t3 t2) t1)) NestLoop(t3 t2) NestLoop(t3 t2 t1) */
t3.shop_id ,t3.shop_nm ,t1.join_tp ,t1.mbr_id ,t2.ord_dtm ,t2.ord_amt
FROM startdbpg.ms_mbr_big t1
INNER JOIN startdbpg.tr_ord_big t2 ON (t2.mbr_id = t1.mbr_id)
INNER JOIN startdbpg.ms_shop_big t3 ON (t3.shop_id = t2.shop_id)
WHERE t1.mbr_gd = 'SILV'
AND t1.join_dtm >= '2022-04-20'::date
AND t1.join_dtm < '2022-05-01'::date
AND t2.ord_dtm >= '2023-12-21'::date
AND t2.ord_dtm < '2023-12-26'::date
AND t3.shop_start_ymd LIKE '202001%'
AND t3.table_qty >= 10
ORDER BY ord_amt DESC;
SQL
복사
