-- [SQL-10-3-2-a]
CREATE INDEX tr_ord_vac_x01 on startdbpg.tr_ord_vac(ord_dtm);
CLUSTER startdbpg.tr_ord_vac USING tr_ord_vac_x01;
VACUUM startdbpg.tr_ord_vac;
ANALYZE startdbpg.tr_ord_vac;
SQL
복사
-- [SQL-10-3-2-b]
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT t1.mbr_id, t1.ord_amt
FROM startdbpg.tr_ord_vac t1
WHERE t1.ord_dtm >= '2024-10-01'::date
AND t1.ord_dtm < '2025-01-01'::date;
SQL
복사
-- [SQL-10-3-2-c]: 의미 없는 전체 UPDATE 세 번 후 [SQL-10-3-1-a]로 용량 확인
UPDATE startdbpg.tr_ord_vac SET ord_st = ord_st;
UPDATE startdbpg.tr_ord_vac SET ord_dtm = ord_dtm;
UPDATE startdbpg.tr_ord_vac SET mbr_id = mbr_id;
-- [SQL-10-3-1-a]
SELECT t1.n_dead_tup ,t1.n_live_tup ,pg_size_pretty(pg_relation_size(t1.relname::text)) tab_sz
FROM pg_stat_user_tables t1
WHERE t1.relid = 'startdbpg.tr_ord_vac'::regclass;
SQL
복사
-- [SQL-10-3-2-d]: [SQL-10-3-2-b]를 재실행
-- [SQL-10-3-2-b]
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT t1.mbr_id, t1.ord_amt
FROM startdbpg.tr_ord_vac t1
WHERE t1.ord_dtm >= '2024-10-01'::date
AND t1.ord_dtm < '2025-01-01'::date;
SQL
복사
-- [SQL-10-3-2-e]: 베큠 후에 [SQL-10-3-2-b]를 재실행
VACUUM startdbpg.tr_ord_vac;
-- [SQL-10-3-2-b]
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT t1.mbr_id, t1.ord_amt
FROM startdbpg.tr_ord_vac t1
WHERE t1.ord_dtm >= '2024-10-01'::date
AND t1.ord_dtm < '2025-01-01'::date;
SQL
복사
-- [SQL-10-3-2-f]: 베큠 풀 후에 [SQL-10-3-2-b]를 재실행
VACUUM FULL startdbpg.tr_ord_vac;
-- [SQL-10-3-2-b]
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT t1.mbr_id, t1.ord_amt
FROM startdbpg.tr_ord_vac t1
WHERE t1.ord_dtm >= '2024-10-01'::date
AND t1.ord_dtm < '2025-01-01'::date;
SQL
복사