8-1-5. 드라이빙 집합 선택

-- [SQL-8-1-5-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     t1.join_dtm >= '2019-01-01'::date
AND     t1.join_dtm <  '2019-02-01'::date
AND     t2.ord_dtm  >= '2024-12-25'::date 
AND     t2.ord_dtm  <  '2024-12-26'::date;
SQL
복사

-- [SQL-8-1-5-b]

SELECT  COUNT(*) cnt -- 135건
FROM    startdbpg.ms_mbr_big t1
WHERE   t1.mbr_gd = 'PLAT'
AND     t1.join_dtm >= '2019-01-01'::date
AND     t1.join_dtm <  '2019-02-01'::date;

SELECT  COUNT(*) cnt -- 27,504건
FROM    startdbpg.tr_ord_big t2
WHERE   t2.ord_dtm  >= '2024-12-25'::date 
AND     t2.ord_dtm  <  '2024-12-26'::date;
SQL
복사

-- [SQL-8-1-5-c]

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  = '2024-12-25 09:00:00.000'::timestamp;
SQL
복사

-- [SQL-8-1-5-d]

SELECT  COUNT(*) cnt -- 11,440건
FROM    startdbpg.ms_mbr_big t1
WHERE   t1.mbr_gd = 'PLAT';

SELECT  COUNT(*) cnt -- 3건
FROM    startdbpg.tr_ord_big t2
WHERE   t2.ord_dtm  = '2024-12-25 09:00:00.000'::timestamp;
SQL
복사

-- [SQL-8-1-5-e]: [SQL-8-1-5-c]에 힌트를 적용

EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT  /*+ Leading((t2 t1)) 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  = '2024-12-25 09:00:00.000'::timestamp;
SQL
복사

-- [SQL-8-1-5-f]: x23 인덱스 생성 후 [SQL-8-1-5-e]를 재실행(원래 힌트를 그대로 유지)

CREATE INDEX tr_ord_big_x23 ON startdbpg.tr_ord_big(ord_dtm);

-- [SQL-8-1-5-e]

EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT  /*+ Leading((t2 t1)) 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  = '2024-12-25 09:00:00.000'::timestamp;
SQL
복사