-- [SQL-8-4-3-a]
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT t1.*
,(SELECT s1.base_cd_nm FROM startdbpg.cm_base_cd s1
WHERE s1.base_cd_dv = 'ord_st' AND s1.base_cd = t1.ord_st
) ord_st_nm
,(SELECT s2.base_cd_nm FROM startdbpg.cm_base_cd s2
WHERE s2.base_cd_dv = 'pay_tp' AND s2.base_cd = t1.pay_tp
) pay_tp_nm
FROM startdbpg.tr_ord_big t1
WHERE t1.ord_dtm >= '2025-01-01'::date
AND t1.ord_dtm < '2025-02-01'::date;
SQL
복사
-- [SQL-8-4-3-b]: [SQL-8-4-3-a]의 스칼라 서브쿼리를 아우터 조인으로 변경
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT /*+ Leading(((t1 t2) t3)) NestLoop(t1 t2) NestLoop(t1 t2 t3) */
t1.*, t2.base_cd_nm ,t3.base_cd_nm
FROM startdbpg.tr_ord_big t1
LEFT OUTER JOIN startdbpg.cm_base_cd t2
ON (t2.base_cd_dv = 'ord_st' AND t2.base_cd = t1.ord_st)
LEFT OUTER JOIN startdbpg.cm_base_cd t3
ON (t3.base_cd_dv = 'pay_tp' AND t3.base_cd = t1.pay_tp)
WHERE t1.ord_dtm >= '2025-01-01'::date
AND t1.ord_dtm < '2025-02-01'::date;
SQL
복사
-- [SQL-8-4-3-c]: [SQL-8-4-3-b]의 힌트를 제거하고 실행
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT t1.*, t2.base_cd_nm ,t3.base_cd_nm
FROM startdbpg.tr_ord_big t1
LEFT OUTER JOIN startdbpg.cm_base_cd t2
ON (t2.base_cd_dv = 'ord_st' AND t2.base_cd = t1.ord_st)
LEFT OUTER JOIN startdbpg.cm_base_cd t3
ON (t3.base_cd_dv = 'pay_tp' AND t3.base_cd = t1.pay_tp)
WHERE t1.ord_dtm >= '2025-01-01'::date
AND t1.ord_dtm < '2025-02-01'::date;
SQL
복사