8-4-1. 인덱스 힌트만으로 조인 알고리즘 선택하기

-- [SQL-8-4-1-a]

SELECT  t1.indexname ,t1.indexdef
FROM    pg_indexes t1
WHERE   t1.tablename IN ('tr_ord_big', 'ms_mbr_big', 'ms_shop_big')
ORDER BY t1.indexname;
SQL
복사

-- [SQL-8-4-1-1-a]

EXPLAIN (COSTS OFF)
SELECT  /*+ IndexScan(t2 ?) */
        t1.shop_id ,t1.shop_nm ,t2.ord_dtm ,t2.ord_no ,t2.ord_amt
FROM    startdbpg.ms_shop_big t1
        LEFT OUTER JOIN startdbpg.tr_ord_big t2
            ON (t1.shop_id = t2.shop_id
                AND t2.ord_dtm >= '2024-06-01'::date
                AND t2.ord_dtm <  '2024-07-01'::date
                )
WHERE   t1.shop_oper_tp = 'FLAG'
AND     t1.chair_qty >= 50
ORDER BY t2.ord_no DESC;
SQL
복사

-- [SQL-8-4-1-2-a]

EXPLAIN (COSTS OFF)
SELECT  /*+ IndexScan(t2 ?) IndexScan(t1 ?) */
        t1.shop_id ,t1.shop_nm ,t2.ord_dtm ,t2.ord_no ,t2.ord_amt
FROM    startdbpg.ms_shop_big t1
        INNER JOIN startdbpg.tr_ord_big t2
            ON (t1.shop_id = t2.shop_id)
        INNER JOIN startdbpg.ms_mbr_big t3
            ON (t3.mbr_id = t2.mbr_id)
WHERE   t2.ord_dtm >= '2024-06-01'::date
AND     t2.ord_dtm <  '2024-07-01'::date
AND     t3.join_tp = 'INV'
AND     t3.mbr_gd = 'PLAT'
ORDER BY t2.ord_no DESC;
SQL
복사

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

EXPLAIN (COSTS OFF)
SELECT  /*+ IndexScan(t2 ?) */
        t1.mbr_id ,t1.nick_nm ,t2.ord_dtm ,t2.ord_no ,t2.ord_amt
FROM    startdbpg.ms_mbr_big t1
        INNER JOIN startdbpg.tr_ord_big t2
            ON (t1.mbr_id = t2.mbr_id
                AND t2.ord_dtm >= '2024-09-01'::date
                AND t2.ord_dtm <  '2024-10-01'::date
                )
WHERE   t1.join_dtm = '2020-05-08'::date;
SQL
복사