- startdbpg에 접속해 아래 SQL들을 순차적으로 실행해 튜닝 테스트용 테이블을 생성합니다.
- 반드시 DBeaver에 Autocommit 상태로 접속해서 작업을 진행합니다.
- DBeaver 상단 메뉴 > 데이터베이스 > 트랜잭션 모드 > Auto-Commit
- 번거럽더라도, 가능하면 SQL을 하나씩 순차적으로 실행하는 것을 권장합니다.
- 특히 중간에 프로시저 생성 부분은 별도 창에서 별도로 실행하는 것을 권장합니다.
- 일부 SQL은 오래 걸립니다. 오래 걸리는 중간에 동일 SQL 창에서 다른 SQL을 실행하면 DBeaver 내부적으로 행이 걸리는 상태가 종종 발생합니다.
- 차분히 기다리면서 한 문장씩 순차적으로 실행하세요.
- 제 개인 환경에서는 아래 스크립트 전부 실행하는 30분 정도 걸렸습니다.
-------------------------------------------------------------------------
-- 튜닝용 테이블 제거
-------------------------------------------------------------------------
DROP TABLE IF EXISTS startdbpg.ms_mbr_big;
DROP TABLE IF EXISTS startdbpg.ms_shop_big;
DROP TABLE IF EXISTS startdbpg.tr_ord_big_tmp;
DROP TABLE IF EXISTS startdbpg.tr_ord_big;
DROP TABLE IF EXISTS startdbpg.tr_ord_det_big;
DROP TABLE IF EXISTS startdbpg.tr_event_entry_big;
-------------------------------------------------------------------------
-- 튜닝용 테이블 생성(startdbpg DB에 접속해서 실행해야함)
-------------------------------------------------------------------------
CREATE TABLE startdbpg.ms_shop_big
(
shop_id VARCHAR(40) NOT NULL
,shop_nm VARCHAR(100) NULL
,shop_size INTEGER NULL
,shop_oper_tp VARCHAR(40) NULL
,table_qty INTEGER NULL
,chair_qty INTEGER NULL
,open_time VARCHAR(100) NULL
,close_time VARCHAR(100) NULL
,shop_st VARCHAR(40) NULL
,shop_start_ymd VARCHAR(8) NULL
,shop_end_ymd VARCHAR(8) NULL
);
CREATE TABLE startdbpg.ms_mbr_big
(
mbr_id VARCHAR(40) NOT NULL
,nick_nm VARCHAR(100) NULL
,mobl_no VARCHAR(100) NULL
,emal_adr VARCHAR(100) NULL
,join_dtm TIMESTAMP WITHOUT TIME ZONE NULL
,join_tp VARCHAR(40) NULL
,mbr_gd VARCHAR(40) NULL
,mbr_st VARCHAR(40) NULL
,leave_dtm TIMESTAMP WITHOUT TIME ZONE NULL
);
CREATE TABLE startdbpg.tr_ord_big_tmp
( ord_no DECIMAL (20,0) NOT NULL
,ord_dtm TIMESTAMP WITHOUT TIME ZONE NULL
,prep_cmp_dtm TIMESTAMP WITHOUT TIME ZONE NULL
,pkup_dtm TIMESTAMP WITHOUT TIME ZONE NULL
,mbr_id VARCHAR (40) NULL
,shop_id VARCHAR (40) NULL
,ord_st VARCHAR (40) NULL
,ord_amt DECIMAL (18,3) NULL
,pay_tp VARCHAR (40) NULL
);
CREATE TABLE startdbpg.tr_ord_big
( ord_no DECIMAL (20,0) NOT NULL
,ord_dtm TIMESTAMP WITHOUT TIME ZONE NULL
,ord_ymd VARCHAR(8) NULL
,prep_cmp_dtm TIMESTAMP WITHOUT TIME ZONE NULL
,pkup_dtm TIMESTAMP WITHOUT TIME ZONE NULL
,mbr_id VARCHAR (40) NULL
,shop_id VARCHAR (40) NULL
,ord_st VARCHAR (40) NULL
,ord_amt DECIMAL (18,3) NULL
,pay_tp VARCHAR (40) NULL
);
CREATE TABLE startdbpg.tr_ord_det_big
( ord_no DECIMAL (20,0) NOT NULL
,ord_det_no DECIMAL (10,0) NOT NULL
,item_id VARCHAR (40) NULL
,ord_qty DECIMAL (10,0) NULL
,sale_prc DECIMAL (18,3) NULL
);
CREATE TABLE startdbpg.tr_event_entry_big
( event_id VARCHAR(40) NOT NULL
,entry_no INTEGER NOT NULL
,shop_id VARCHAR(40) NOT NULL
,mbr_id VARCHAR(40) NOT NULL
,entry_dtm TIMESTAMP WITHOUT TIME ZONE NOT NULL
,entry_rslt_cd VARCHAR(40) NOT NULL
,entry_rslt_dtm TIMESTAMP WITHOUT TIME ZONE NOT NULL
);
-------------------------------------------------------------------------
-- ms_shop -> ms_shop_big 생성
-------------------------------------------------------------------------
INSERT INTO startdbpg.ms_shop_big
(
shop_id
,shop_nm
,shop_size
,shop_oper_tp
,table_qty
,chair_qty
,open_time
,close_time
,shop_st
,shop_start_ymd
,shop_end_ymd
)
SELECT shop_id
,shop_nm
,shop_size
,shop_oper_tp
,table_qty
,chair_qty
,open_time
,close_time
,shop_st
,shop_start_ymd
,shop_end_ymd
FROM startdbpg.ms_shop;
-------------------------------------------------------------------------
-- ms_mbr -> ms_mbr_big 생성
-------------------------------------------------------------------------
INSERT INTO startdbpg.ms_mbr_big
(mbr_id ,nick_nm ,mobl_no ,emal_adr ,join_dtm, join_tp, mbr_gd ,mbr_st, leave_dtm)
SELECT 'M' || LPAD(t1.rno::VARCHAR,5,'0')
,t1.nick_nm || CASE WHEN t1.rno_by_mbr = 0 THEN '' ELSE '-' || t1.rno_by_mbr::VARCHAR END nick_nm
,'100' || LPAD(t1.rno::VARCHAR,5,'0') mobl_no
,LPAD(t1.rno::VARCHAR,5,'0') || '@gm.com' emal_adr
,t1.join_dtm - ((t1.rno_by_mbr+MOD(t1.rno,60))|| 'day')::INTERVAL join_dtm
,CASE WHEN MOD(rno,10) <= 6 THEN 'DRCT'
WHEN MOD(rno,10) <= 8 THEN 'SNS'
ELSE 'INV' END join_tp
,t1.mbr_gd
,t1.mbr_st
,t1.leave_dtm
FROM (
SELECT t1.mbr_id
,t1.nick_nm
,t1.mobl_no
-- 위에서 신규로 부여 ,t1.emal_adr
,t1.join_dtm
,t1.mbr_gd
,t1.mbr_st
,t1.leave_dtm
,ROW_NUMBER() OVER(ORDER BY t1.mbr_id ,t2.rno) rno
,ROW_NUMBER() OVER(PARTITION BY t1.mbr_id ORDER BY t2.rno asc)-1 rno_by_mbr
FROM startdbpg.ms_mbr t1
,(SELECT generate_series(0,9) rno) t2
) t1;
-------------------------------------------------------------------------
-- tr_ord_big_tmp 데이터 생성 프로시저 생성(월별로 반복 실행되는 프로시저)
-- 다른 SQL과 같이 실행하면 정상적으로 생성안될수 있음. 별도 창에서 단독 실행을 권장함.
-------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE startdbpg.usp_insert_tr_ord_big_tmp()
LANGUAGE plpgsql
AS $$
DECLARE
v_yyyymm text;
BEGIN
-- 월별로 그룹화한 yyyymm 목록을 추출
FOR v_yyyymm IN
SELECT to_char(ord_dtm, 'YYYYMM') AS yyyymm
FROM startdbpg.tr_ord
GROUP BY to_char(ord_dtm, 'YYYYMM')
ORDER BY to_char(ord_dtm, 'YYYYMM')
LOOP
-- 작업할 월 출력
RAISE NOTICE '지금 %월 작업 중...', v_yyyymm;
-- 실제 데이터 처리 (월 단위)
INSERT INTO startdbpg.tr_ord_big_tmp
(ord_no, ord_dtm, prep_cmp_dtm, pkup_dtm, mbr_id, shop_id, ord_st, ord_amt, pay_tp)
SELECT row_number() OVER(ORDER BY T3.new_ord_dtm, T3.shop_id, T3.new_mbr_id)
+ COALESCE((SELECT MAX(x.ord_no) FROM tr_ord_big_tmp x), 0) AS new_ord_no
,new_ord_dtm
,new_prep_cmp_dtm
,new_pkup_dtm
,new_mbr_id
,shop_id
,ord_st
,NULL AS ord_amt
,pay_tp
FROM (
SELECT t1.ord_dtm + (t2.n || ' second')::interval AS new_ord_dtm
,t1.prep_cmp_dtm + (t2.n || ' second')::interval AS new_prep_cmp_dtm
,t1.pkup_dtm + (t2.n || ' second')::interval AS new_pkup_dtm
,'M' || right(
'00000'
|| (
(
substring(t1.mbr_id, 2, 4)::integer
+ (t1.ord_no % 999 + 1)
)
* (t2.n + 1) % 99990
+ 1
)::VARCHAR
,5) AS new_mbr_id
,t1.shop_id
,t1.ord_st
,NULL AS ord_amt
,t1.pay_tp
FROM startdbpg.tr_ord t1
CROSS JOIN generate_series(0, 35) AS t2(n)
WHERE
-- 여기서 v_yyyymm 기준으로 해당 월 데이터만 추출
t1.ord_dtm >= to_date(v_yyyymm, 'YYYYMM')
AND t1.ord_dtm < to_date(v_yyyymm, 'YYYYMM') + INTERVAL '1 month'
) T3;
-- 월별 처리가 끝날 때마다 커밋
COMMIT;
END LOOP;
END;
$$;
-------------------------------------------------------------------------
-- tr_ord_big_tmp 데이터 생성 프로시저 실행(Output 탭으로 진행중 내용 확인 가능)
-------------------------------------------------------------------------
-- 약 5분
CALL startdbpg.usp_insert_tr_ord_big_tmp();
-------------------------------------------------------------------------
-- tr_ord_big_tmp -> tr_ord_det_big 데이터 생성
-------------------------------------------------------------------------
-- 약 2분
-- 첫 번째 INSERT (1/2) / 14,001,245 건
INSERT INTO startdbpg.tr_ord_det_big(ord_no, ord_det_no, item_id, ord_qty, sale_prc)
SELECT T1.ord_no
,ROW_NUMBER() OVER(PARTITION BY T1.ord_no ORDER BY t1.item_id) ord_det_no
,t1.item_id
,SUM(t1.ord_qty) ord_qty
,MAX(t1.sale_prc) sale_prc
FROM (
SELECT T1.ord_no ,1 ord_det_no ,T2.item_id ,1 ord_qty ,T2.sale_prc
FROM startdbpg.tr_ord_big_tmp T1
INNER JOIN
(
SELECT t1.item_id ,t1.prc_start_dt ,t1.prc_end_dt
,ROW_NUMBER() OVER(ORDER BY t1.prc_start_dt ,t1.prc_end_dt , t1.item_id) item_no
,t1.sale_prc
FROM startdbpg.ms_item_prc_hist t1
WHERE t1.item_id IN ('AMB','AMR','IAMB','IAMR','CLB','CLR','ICLB','ICLR')
AND t1.prc_start_dt < TO_DATE('2023-01-01','YYYY-MM-DD')
) T2 ON (T2.item_no = MOD(T1.ord_no,8) + 1)
WHERE T1.ord_dtm >= T2.prc_start_dt
AND T1.ord_dtm < T2.prc_end_dt + interval '1 day'
AND T1.ord_dtm < TO_DATE('2023-01-01','YYYY-MM-DD')
UNION ALL
SELECT T1.ord_no ,2 ord_det_no ,T2.item_id ,1 ord_qty ,T2.sale_prc
FROM startdbpg.tr_ord_big_tmp T1
INNER JOIN
(
SELECT t1.item_id ,t1.prc_start_dt ,t1.prc_end_dt
,ROW_NUMBER() OVER(ORDER BY t1.item_id DESC, t1.prc_start_dt ,t1.prc_end_dt) item_no
,t1.sale_prc
FROM startdbpg.ms_item_prc_hist t1
) T2 ON (T2.item_no = MOD(T1.ord_no,44) + 1)
WHERE T1.ord_dtm >= T2.prc_start_dt
AND T1.ord_dtm < T2.prc_end_dt + interval '1 day'
AND T1.ord_dtm < TO_DATE('2023-01-01','YYYY-MM-DD')
) t1
GROUP BY t1.ord_no ,t1.item_id;
-- 약 2분
-- 두 번째 INSERT (2/2) / 22,189,484 건
INSERT INTO startdbpg.tr_ord_det_big(ord_no, ord_det_no, item_id, ord_qty, sale_prc)
SELECT T1.ord_no
,ROW_NUMBER() OVER(PARTITION BY T1.ord_no ORDER BY t1.item_id) ord_det_no
,t1.item_id
,SUM(t1.ord_qty) ord_qty
,MAX(t1.sale_prc) sale_prc
FROM (
SELECT T1.ord_no ,1 ord_det_no ,T2.item_id ,1 ord_qty ,T2.sale_prc
FROM startdbpg.tr_ord_big_tmp T1
INNER JOIN
(
SELECT t1.item_id ,t1.prc_start_dt ,t1.prc_end_dt
,ROW_NUMBER() OVER(ORDER BY t1.prc_start_dt ,t1.prc_end_dt , t1.item_id) item_no
,t1.sale_prc
FROM startdbpg.ms_item_prc_hist t1
WHERE t1.item_id IN ('AMB','AMR','IAMB','IAMR','CLB','CLR','ICLB','ICLR')
AND t1.prc_start_dt >= TO_DATE('2023-01-01','YYYY-MM-DD')
) T2 ON (T2.item_no = MOD(T1.ord_no,8) + 1)
WHERE T1.ord_dtm >= T2.prc_start_dt
AND T1.ord_dtm < T2.prc_end_dt + interval '1 day'
AND T1.ord_dtm >= TO_DATE('2023-01-01','YYYY-MM-DD')
UNION ALL
SELECT T1.ord_no ,2 ord_det_no ,T2.item_id ,1 ord_qty ,T2.sale_prc
FROM startdbpg.tr_ord_big_tmp T1
INNER JOIN
(
SELECT t1.item_id ,t1.prc_start_dt ,t1.prc_end_dt
,ROW_NUMBER() OVER(ORDER BY t1.item_id DESC, t1.prc_start_dt ,t1.prc_end_dt) item_no
,t1.sale_prc
FROM startdbpg.ms_item_prc_hist t1
) T2 ON (T2.item_no = MOD(T1.ord_no,44) + 1)
WHERE T1.ord_dtm >= T2.prc_start_dt
AND T1.ord_dtm < T2.prc_end_dt + interval '1 day'
AND T1.ord_dtm >= TO_DATE('2023-01-01','YYYY-MM-DD')
) t1
GROUP BY t1.ord_no ,t1.item_id;
-------------------------------------------------------------------------
-- tr_ord_big_tmp + tr_ord_det_big -> tr_ord_big 데이터 생성
-------------------------------------------------------------------------
-- 약 3분
-- 26,291,772 건
INSERT INTO startdbpg.tr_ord_big(ord_no,ord_dtm,ord_ymd,prep_cmp_dtm,pkup_dtm,mbr_id,shop_id,ord_st,ord_amt,pay_tp)
SELECT t1.ord_no ,t1.ord_dtm, TO_CHAR(t1.ord_dtm,'YYYYMMDD') ord_ymd ,t1.prep_cmp_dtm ,t1.pkup_dtm, t1.mbr_id ,t1.shop_id ,t1.ord_st
,t2.ord_amt ,t1.pay_tp
FROM startdbpg.tr_ord_big_tmp t1
INNER JOIN (
select x.ord_no , sum(x.ord_qty * x.sale_prc) ord_amt
from startdbpg.tr_ord_det_big x
group by x.ord_no
) t2
on (t1.ord_no = t2.ord_no)
;
-------------------------------------------------------------------------
-- tr_ord_big_tmp 제거
-------------------------------------------------------------------------
DROP TABLE startdbpg.tr_ord_big_tmp;
-------------------------------------------------------------------------
-- tr_ord_big -> tr_event_entry_big 생성
-------------------------------------------------------------------------
-- 약 4분
-- 21,474,788 건
INSERT INTO startdbpg.tr_event_entry_big
(
event_id
,entry_no
,shop_id
,mbr_id
,entry_dtm
,entry_rslt_cd
,entry_rslt_dtm
)
SELECT t3.event_id
,t3.entry_no
,t3.shop_id
,t3.mbr_id
,t3.entry_dtm + (entry_no * interval '1 second') entry_dtm
,t3.entry_rslt_cd
,t3.entry_rslt_dtm
FROM (
SELECT t2.event_id
,ROW_NUMBER() OVER(PARTITION BY t2.event_id ORDER BY t2.entry_dtm, t2.mbr_id ,t2.shop_id) entry_no
,t2.shop_id
,t2.mbr_id
,t2.entry_dtm
-- ,t2.entry_dtm + ((ROW_NUMBER() OVER(PARTITION BY t2.event_id ORDER BY t2.ord_no ASC)) * interval '1 second') entry_dtm
,CASE WHEN MOD(TO_CHAR(entry_dtm,'MISS')::integer,5) = 0 THEN 'SUCC' ELSE 'FAIL' END entry_rslt_cd
,DATE_TRUNC('hour', t2.entry_dtm) + INTERVAL '3 day' entry_rslt_dtm
FROM (
SELECT 'EV' || t1.shop_id || TO_CHAR(t1.ord_dtm,'YYMMDDHH24') event_id
,MIN(t1.shop_id) shop_id
,t1.mbr_id
,MIN(t1.ord_dtm) entry_dtm
,MIN(t1.ord_no) ord_no
FROM startdbpg.tr_ord_big t1
WHERE TO_CHAR(t1.ord_dtm,'HH24') BETWEEN '07' AND '20'
GROUP BY 'EV' || t1.shop_id || TO_CHAR(t1.ord_dtm,'YYMMDDHH24')
,t1.mbr_id
) t2
) t3
;
-------------------------------------------------------------------------
-- PK 생성
-------------------------------------------------------------------------
ALTER TABLE startdbpg.ms_shop_big ADD CONSTRAINT ms_shop_big_pk PRIMARY KEY(shop_id);
ALTER TABLE startdbpg.ms_mbr_big ADD CONSTRAINT ms_mbr_big_pk PRIMARY KEY(mbr_id);
ALTER TABLE startdbpg.tr_ord_big ADD CONSTRAINT tr_ord_big_pk PRIMARY KEY(ord_no); -- 약 20초
ALTER TABLE startdbpg.tr_ord_det_big ADD CONSTRAINT tr_ord_det_big_pk PRIMARY KEY(ord_no,ord_det_no); -- 약 20초
ALTER TABLE startdbpg.tr_event_entry_big ADD CONSTRAINT tr_event_entry_big_pk PRIMARY KEY(event_id,entry_no); -- 약 20초
-------------------------------------------------------------------------
-- 클러스터 및 통계
-------------------------------------------------------------------------
-- 클러스터 작업은 비교적 많은 시간이 걸립니다. 차분하게 기다리며 작업하세요.
CLUSTER startdbpg.ms_mbr_big USING ms_mbr_big_pk;
CLUSTER startdbpg.ms_shop_big USING ms_shop_big_pk;
CLUSTER startdbpg.tr_ord_big USING tr_ord_big_pk; -- 약 2분
CLUSTER startdbpg.tr_ord_det_big USING tr_ord_det_big_pk; -- 약 2분
CREATE INDEX tx01 ON startdbpg.tr_event_entry_big(entry_dtm); -- 임시 인덱스(For Cluster), 약 10초
CLUSTER startdbpg.tr_event_entry_big USING tx01; -- 약 2분
DROP INDEX startdbpg.tx01; -- 임시 인덱스 삭제
VACUUM startdbpg.ms_mbr_big;
VACUUM startdbpg.ms_shop_big;
VACUUM startdbpg.tr_ord_big;
VACUUM startdbpg.tr_ord_det_big;
VACUUM startdbpg.tr_event_entry_big;
ANALYZE startdbpg.ms_mbr_big;
ANALYZE startdbpg.ms_shop_big;
ANALYZE startdbpg.tr_ord_big;
ANALYZE startdbpg.tr_ord_det_big;
ANALYZE startdbpg.tr_event_entry_big;