8-3-6. 해시 조인은 동등 조인 조건이 필수

-- [SQL-8-3-6-a]

SELECT  t1.ord_no ,md5(t1.ord_no::text) as hash
FROM    startdbpg.tr_ord_big t1
WHERE   t1.ord_no BETWEEN 9 AND 12
ORDER BY t1.ord_no;
SQL
복사

-- [SQL-8-3-6-b]

EXPLAIN (ANALYZE, COSTS OFF)
SELECT  /*+ Leading((t1 t3)) HashJoin(t1 t3) */
        t1.ord_no ,t1.ord_dtm ,t1.ord_amt
        ,t3.item_id ,t3.prc_start_dt ,t3.prc_end_dt ,t3.sale_prc
FROM    startdbpg.tr_ord_big t1
        INNER JOIN startdbpg.ms_item_prc_hist t3
            ON (t3.prc_start_dt <= t1.ord_dtm::date
            AND t3.prc_end_dt   >= t1.ord_dtm::date)
WHERE   t1.ord_dtm >= '2023-01-01'::date
AND     t1.ord_dtm <  '2023-02-01'::date;
SQL
복사

-- [SQL-8-3-6-c]

EXPLAIN (ANALYZE,COSTS OFF)
SELECT  t1.ord_no ,t1.ord_dtm ,t1.ord_amt
        ,t3.item_id ,t3.prc_start_dt ,t3.prc_end_dt ,t3.sale_prc
FROM    startdbpg.tr_ord_big t1
        INNER JOIN startdbpg.tr_ord_det_big t2  -- > 주문상세 조인 추가
            ON (t2.ord_no = t1.ord_no)          -- > 주문상세 조인 조건 추가
        INNER JOIN startdbpg.ms_item_prc_hist t3
            ON (t3.prc_start_dt <= t1.ord_dtm::date
            AND t3.prc_end_dt   >= t1.ord_dtm::date
            AND t3.item_id = t2.item_id)         -- > 상품ID 조인 조건 추가
WHERE   t1.ord_dtm >= '2023-01-01'::date
AND     t1.ord_dtm <  '2023-02-01'::date;
SQL
복사

-- [SQL-8-3-6-d]: [SQL-8-3-6-c]의 성능을 개선하기 위해 힌트 적용

EXPLAIN (ANALYZE,COSTS OFF)
SELECT  /*+ Leading(((t1 t2) t3)) NestLoop(t1 t2) HashJoin(t1 t2 t3) */
        t1.ord_no ,t1.ord_dtm ,t1.ord_amt
        ,t3.item_id ,t3.prc_start_dt ,t3.prc_end_dt ,t3.sale_prc
FROM    startdbpg.tr_ord_big t1
        INNER JOIN startdbpg.tr_ord_det_big t2  -- > 주문상세 조인 추가
            ON (t2.ord_no = t1.ord_no)          -- > 주문상세 조인 조건 추가
        INNER JOIN startdbpg.ms_item_prc_hist t3
            ON (t3.prc_start_dt <= t1.ord_dtm::date
            AND t3.prc_end_dt   >= t1.ord_dtm::date
            AND t3.item_id = t2.item_id)         -- > 상품ID 조인 조건 추가
WHERE   t1.ord_dtm >= '2023-01-01'::date
AND     t1.ord_dtm <  '2023-02-01'::date;
SQL
복사