1-2-2. 실습환경 구성하기

-- [SQL-1-2-2-1-a] CREATE DATABASE startdbpg WITH TEMPLATE template0 ENCODING 'UTF8' LC_COLLATE 'C' LC_CTYPE 'C';

-- [SQL-1-2-2-3-a] SELECT 'DROP INDEX startdbpg.' || t1.indexname || ';' script FROM pg_indexes t1 WHERE t1.tablename IN ('tr_ord_big','ms_mbr_big','cm_base_cd','ms_shop_big') AND t1.indexname != t1.tablename || '_pk' AND t1.indexname NOT LIKE t1.tablename || '_fk%' ORDER BY t1.indexname;

-- [SQL-1-2-2-4-a] Chapter 3부터 Chapter 6까지 인덱스 -- [SQL-3-1-3-a] CREATE INDEX tr_ord_big_x01 ON startdbpg.tr_ord_big(ord_dtm); -- [SQL-5-1-4-b] CREATE INDEX tr_ord_big_x02 ON startdbpg.tr_ord_big(shop_id); -- [SQL-5-2-2-a] CREATE INDEX tr_ord_big_x03 ON startdbpg.tr_ord_big(ord_dtm, mbr_id); -- [SQL-5-2-4-a] CREATE INDEX tr_ord_big_x04 ON startdbpg.tr_ord_big(mbr_id, ord_dtm); -- [SQL-5-3-2-c] CREATE INDEX tr_ord_big_x05 ON startdbpg.tr_ord_big(ord_ymd); -- [SQL-5-3-7-b] CREATE INDEX ms_mbr_big_x01 ON startdbpg.ms_mbr_big(nick_nm); -- [SQL-5-4-1-2-c] CREATE INDEX cm_base_cd_x01 ON startdbpg.cm_base_cd(base_cd_dv,base_cd) INCLUDE(base_cd_nm); -- [SQL-5-4-1-3-b] CREATE INDEX tr_ord_big_x06 ON startdbpg.tr_ord_big(mbr_id, ord_dtm) INCLUDE(shop_id, ord_amt); -- [SQL-5-4-2-c] CREATE INDEX tr_ord_big_x07 ON startdbpg.tr_ord_big(shop_id) WHERE ord_st = 'PREP'; -- [SQL-5-4-5-1-b] CREATE INDEX ms_mbr_big_fx01 ON startdbpg.ms_mbr_big(LOWER(mbr_id)); -- [SQL-6-2-4-1-b] CREATE INDEX tr_ord_big_x08 ON startdbpg.tr_ord_big(shop_id, ord_dtm); -- [SQL-6-2-4-2-b] CREATE INDEX tr_ord_big_x09 ON startdbpg.tr_ord_big(shop_id, prep_cmp_dtm, ord_no); -- [SQL-6-3-5-e] CREATE INDEX tr_ord_big_x10 ON startdbpg.tr_ord_big(mbr_id, ord_no);

-- [SQL-1-2-2-4-b] Chapter 8 이후의 인덱스 -- [SQL-8-1-2-a] CREATE INDEX tr_ord_big_x21 ON startdbpg.tr_ord_big(mbr_id); -- [SQL-8-1-3-a] CREATE INDEX tr_ord_big_x22 ON startdbpg.tr_ord_big(mbr_id, ord_dtm); -- [SQL-8-1-4-b] CREATE INDEX ms_mbr_big_x21 ON startdbpg.ms_mbr_big(mbr_gd, join_tp); -- [SQL-8-1-5-f] CREATE INDEX tr_ord_big_x23 ON startdbpg.tr_ord_big(ord_dtm); -- [SQL-8-1-8-d] CREATE INDEX tr_ord_big_x24 ON startdbpg.tr_ord_big(shop_id, ord_dtm); -- [SQL-8-2-2-a] CREATE INDEX tr_ord_big_x25 ON startdbpg.tr_ord_big(shop_id, mbr_id); -- [SQL-8-3-2-b] CREATE INDEX tr_ord_big_x26 ON startdbpg.tr_ord_big(prep_cmp_dtm); -- [SQL-8-4-5-b] CREATE INDEX tr_ord_big_x27 ON startdbpg.tr_ord_big(ord_st, pkup_dtm);