-- [SQL-8-4-4-a]
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT t1.shop_id ,TO_CHAR(t2.ord_dtm,'YYYYMM') ord_ym
,MAX(t1.shop_nm) shop_nm ,SUM(t2.ord_amt) ord_amt_sum
FROM startdbpg.ms_shop_big t1
INNER JOIN startdbpg.tr_ord_big t2
ON (t1.shop_id = t2.shop_id)
WHERE t2.ord_dtm >= '2024-01-01'::date
AND t2.ord_dtm < '2024-04-01'::date
GROUP BY t1.shop_id ,TO_CHAR(t2.ord_dtm,'YYYYMM')
ORDER BY t1.shop_id ,TO_CHAR(t2.ord_dtm,'YYYYMM');
SQL
복사
-- [SQL-8-4-4-b]: [SQL-8-4-4-a]를 GROUP BY 후 조인 패턴으로 변경
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT t1.shop_id ,t2.ym
,t1.shop_nm ,t2.ord_amt_sum
FROM startdbpg.ms_shop_big t1
INNER JOIN (
SELECT a.shop_id ,TO_CHAR(a.ord_dtm,'YYYYMM') ym
,SUM(a.ord_amt) ord_amt_sum
FROM startdbpg.tr_ord_big a
WHERE a.ord_dtm >= '2024-01-01'::date
AND a.ord_dtm < '2024-04-01'::date
GROUP BY a.shop_id ,TO_CHAR(a.ord_dtm,'YYYYMM')
) t2
ON (t1.shop_id = t2.shop_id)
ORDER BY t1.shop_id ,t2.ym;
SQL
복사
-- [SQL-8-4-4-c]: [SQL-8-4-4-b]에 힌트 적용
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT /*+ HashJoin(t1 t2) */
t1.shop_id ,t2.ym
,t1.shop_nm ,t2.ord_amt_sum
FROM startdbpg.ms_shop_big t1
INNER JOIN (
SELECT a.shop_id ,TO_CHAR(a.ord_dtm,'YYYYMM') ym
,SUM(a.ord_amt) ord_amt_sum
FROM startdbpg.tr_ord_big a
WHERE a.ord_dtm >= '2024-01-01'::date
AND a.ord_dtm < '2024-04-01'::date
GROUP BY a.shop_id ,TO_CHAR(a.ord_dtm,'YYYYMM')
) t2
ON (t1.shop_id = t2.shop_id)
ORDER BY t1.shop_id ,t2.ym;
SQL
복사