-- [SQL-6-2-4-1-a]
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT t1.*
FROM startdbpg.tr_ord_big t1
WHERE t1.shop_id = 'S286'
AND t1.ord_dtm >= '2023-01-01'::date
AND t1.ord_dtm < '2023-02-01'::date
ORDER BY t1.ord_dtm ASC
LIMIT 3;
SQL
복사
-- [SQL-6-2-4-1-b]: x08 인덱스 생성후 [SQL-6-2-4-1-a]를 재실행
CREATE INDEX tr_ord_big_x08 ON startdbpg.tr_ord_big(shop_id, ord_dtm);
-- [SQL-6-2-4-1-a]
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT t1.*
FROM startdbpg.tr_ord_big t1
WHERE t1.shop_id = 'S286'
AND t1.ord_dtm >= '2023-01-01'::date
AND t1.ord_dtm < '2023-02-01'::date
ORDER BY t1.ord_dtm ASC
LIMIT 3;
SQL
복사
-- [SQL-6-2-4-2-a]
SELECT t1.*
FROM startdbpg.tr_ord_big t1
WHERE t1.shop_id = 'S025'
AND t1.prep_cmp_dtm >= '2023-02-01'::date
AND t1.prep_cmp_dtm < '2023-03-01'::date
ORDER BY t1.prep_cmp_dtm DESC ,t1.ord_no DESC
LIMIT 10;
SQL
복사
-- [SQL-6-2-4-2-b]: x09 인덱스 생성후 [SQL-6-2-4-2-a]를 재실행
CREATE INDEX tr_ord_big_x09 ON startdbpg.tr_ord_big(shop_id, prep_cmp_dtm, ord_no);
-- [SQL-6-2-4-2-a]
EXPLAIN(ANALYZE,BUFFERS,COSTS OFF)
SELECT t1.*
FROM startdbpg.tr_ord_big t1
WHERE t1.shop_id = 'S025'
AND t1.prep_cmp_dtm >= '2023-02-01'::date
AND t1.prep_cmp_dtm < '2023-03-01'::date
ORDER BY t1.prep_cmp_dtm DESC ,t1.ord_no DESC
LIMIT 10;
SQL
복사
-- [SQL-6-2-4-3-a]
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT /*+ IndexScan(t1 tr_ord_big_x04) */
t1.*
FROM startdbpg.tr_ord_big t1
WHERE t1.mbr_id >= 'M00100'
AND t1.mbr_id < 'M00110'
ORDER BY t1.ord_dtm;
SQL
복사
-- [SQL-6-2-4-4-a]: [SQL-6-2-4-2-a]에 mbr_id 조건 추가
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT t1.*
FROM startdbpg.tr_ord_big t1
WHERE t1.shop_id = 'S025'
AND t1.prep_cmp_dtm >= '2023-02-01'::date
AND t1.prep_cmp_dtm < '2023-03-01'::date
AND t1.mbr_id = 'M09577' -- > 신규 요구 사항: mbr_id 조건 추가
ORDER BY t1.prep_cmp_dtm DESC, t1.ord_no DESC
LIMIT 10;
SQL
복사
-- [SQL-6-2-4-4-b]: [SQL-6-2-4-2-a]의 ORDER BY 변경
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT t1.*
FROM startdbpg.tr_ord_big t1
WHERE t1.shop_id = 'S025'
AND t1.prep_cmp_dtm >= '2023-02-01'::date
AND t1.prep_cmp_dtm < '2023-03-01'::date
ORDER BY t1.ord_no DESC, t1.prep_cmp_dtm DESC -- > 신규 요구 사항: ORDER BY 기준 변경
LIMIT 10;
SQL
복사