10-3-1. UPDATE와 용량 변화

-- [SQL-10-3-a]

DROP TABLE IF EXISTS startdbpg.tr_ord_vac;

CREATE TABLE startdbpg.tr_ord_vac AS
SELECT  ROW_NUMBER() OVER(ORDER BY t1.ord_no, t2.rno) ord_no
        ,t1.ord_dtm ,t1.prep_cmp_dtm ,t1.pkup_dtm ,t1.mbr_id 
        ,t1.shop_id ,t1.ord_st ,t1.ord_amt ,t1.pay_tp
FROM    startdbpg.tr_ord t1
        ,(SELECT generate_series(0,10) rno) t2;

ALTER TABLE startdbpg.tr_ord_vac ADD CONSTRAINT tr_ord_vac_pk PRIMARY KEY(ord_no);
ALTER TABLE startdbpg.tr_ord_vac SET (autovacuum_enabled = false);
SQL
복사

-- [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-1-b]: UPDATE 수행 후 [SQL-10-3-1-a]를 재실행

UPDATE startdbpg.tr_ord_vac SET ord_st = ord_st;

-- [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-1-c]: 베큠 수행 후 [SQL-10-3-1-a]를 실행

VACUUM startdbpg.tr_ord_vac;

-- [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-1-d]: 베큠 풀 수행 후 [SQL-10-3-1-a]를 실행

VACUUM FULL tr_ord_vac;

-- [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
복사