8-3-1. 대용량 조인에 탁월한 해시 조인

-- [SQL-8-3-1-a]

EXPLAIN (ANALYZE,COSTS OFF)
SELECT  /*+ NestLoop(t1 t2) */
        t1.mbr_id ,t1.nick_nm ,t2.ord_dtm ,t2.ord_amt
FROM    startdbpg.ms_mbr_big t1
        INNER JOIN startdbpg.tr_ord_big t2
          ON (t1.mbr_id =  t2.mbr_id) 
WHERE   t1.mbr_gd = 'PLAT' 
AND     t2.ord_dtm >= '2023-01-01'::date
AND     t2.ord_dtm <  '2025-01-01'::date;
SQL
복사

-- [SQL-8-3-1-b]: [SQL-8-3-1-a]의 힌트만 MergeJoin으로 변경하여 실행

EXPLAIN (ANALYZE,COSTS OFF)
SELECT  /*+ MergeJoin(t1 t2) */
        t1.mbr_id ,t1.nick_nm ,t2.ord_dtm ,t2.ord_amt
FROM    startdbpg.ms_mbr_big t1
        INNER JOIN startdbpg.tr_ord_big t2
          ON (t1.mbr_id =  t2.mbr_id) 
WHERE   t1.mbr_gd = 'PLAT' 
AND     t2.ord_dtm >= '2023-01-01'::date
AND     t2.ord_dtm <  '2025-01-01'::date;
SQL
복사

-- [SQL-8-3-1-c]: [SQL-8-3-1-a]의 힌트를 제거

EXPLAIN (ANALYZE,COSTS OFF)
SELECT  t1.mbr_id ,t1.nick_nm ,t2.ord_dtm ,t2.ord_amt
FROM    startdbpg.ms_mbr_big t1
        INNER JOIN startdbpg.tr_ord_big t2
          ON (t1.mbr_id =  t2.mbr_id) 
WHERE   t1.mbr_gd = 'PLAT' 
AND     t2.ord_dtm >= '2023-01-01'::date
AND     t2.ord_dtm <  '2025-01-01'::date;
SQL
복사