-- [SQL-8-1-4-a]
SELECT COUNT(*) cnt -- 11,440
FROM startdbpg.ms_mbr_big t1
WHERE t1.mbr_gd = 'PLAT';
SELECT COUNT(*) cnt -- 9,999
FROM startdbpg.ms_mbr_big t1
WHERE t1.join_tp = 'INV';
SELECT COUNT(*) cnt -- 1,144
FROM startdbpg.ms_mbr_big t1
WHERE t1.mbr_gd = 'PLAT'
AND t1.join_tp = 'INV';
SQL
복사
-- [SQL-8-1-4-b]: x21 인덱스 생성 후 [SQL-8-1-1-a]를 재실행
CREATE INDEX ms_mbr_big_x21 ON startdbpg.ms_mbr_big(mbr_gd, join_tp);
-- [SQL-8-1-1-a]
EXPLAIN (ANALYZE,COSTS OFF)
SELECT /*+ Leading((t1 t2)) 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 (t2.mbr_id = t1.mbr_id)
WHERE t1.mbr_gd = 'PLAT'
AND t1.join_tp = 'INV'
AND t2.ord_dtm >= '2023-12-21'::date
AND t2.ord_dtm < '2023-12-26'::date;
SQL
복사
