•
PG startdb 에 접속해 아래 SQL들을 순차적으로 실행해 튜닝 테스트용 테이블을 생성합니다.
-- ord_big_tmp와 ord_big 테이블 생성
CREATE TABLE ord_big_tmp (ordno decimal (20,0) NOT NULL
,orddtm timestamp NULL
,preparecmpdtm timestamp NULL
,pickupdtm timestamp NULL
,memberid varchar (40) NULL
,shopid varchar (40) NULL
,ordst varchar (40) NULL
,ordamt decimal (18,3) NULL
,paytp varchar (40) NULL
);
CREATE TABLE ord_big (ordno decimal (20,0) NOT NULL
,orddtm timestamp NULL
,preparecmpdtm timestamp NULL
,pickupdtm timestamp NULL
,memberid varchar (40) NULL
,shopid varchar (40) NULL
,ordst varchar (40) NULL
,ordamt decimal (18,3) NULL
,paytp varchar (40) NULL
);
-- ord_big_tmp 데이터 생성 20,795,823 건 생성(나중에 DROP)
INSERT INTO ord_big_tmp
(ordno ,orddtm, preparecmpdtm, pickupdtm, memberid, shopid, ordst, ordamt, paytp)
SELECT row_number() OVER(ORDER BY t3.new_orddtm, t3.new_shopid,t3.new_memberid) new_ordno
,new_orddtm
,new_preparecmpdtm
,new_pickupdtm
,new_memberid
,new_shopid
,ordst
,null ordamt
,paytp
FROM (
SELECT t1.orddtm + (t2.n || ' second')::interval new_orddtm
,t1.preparecmpdtm + (t2.n || ' second')::interval new_preparecmpdtm
,t1.pickupdtm + (t2.n || ' second')::interval new_pickupdtm
,'M' ||
LPAD(
(CASE WHEN (substring(t1.memberid,2,4)::integer + T2.n) >= 9999 THEN 9999
ELSE (substring(t1.memberid,2,4)::integer + T2.n) END)::varchar, 4, '0') new_memberid
,'S' ||
LPAD(
(CASE WHEN (substring(t1.shopid,2,3)::integer + T2.n) >= 300 THEN 300
ELSE (substring(t1.shopid,2,3)::integer + T2.n) END)::varchar, 3, '0') new_shopid
,t1.shopid
,t1.ordst
,NULL ordamt
,t1.paytp
FROM ord t1
CROSS JOIN (
select generate_series(0, 30) as n
) t2
) T3;
-- orddet_big 생성
CREATE TABLE orddet_big (ordno decimal (20,0) NOT NULL
,orddetno decimal (10,0) NOT NULL
,itemid varchar (40) NULL
,ordqty decimal (10,0) NULL
,saleprc decimal (18,3) NULL );
-- orddet_big 데이터 생성(20230401 이전)
INSERT INTO orddet_big(OrdNo, OrdDetNo, ItemId, OrdQty, SalePrc)
WITH W01 AS(
SELECT T1.OrdNo ,1 OrdDetNo ,T2.ItemId ,1 OrdQty ,T1.OrdDtm
FROM ord_big_tmp T1
INNER JOIN
(
SELECT x.itemId, ROW_NUMBER() OVER(ORDER BY x.itemID) ItemNo
FROM Item x
WHERE x.launchdt = TO_DATE('20190101','YYYYMMDD')
) T2 ON (T2.ItemNo = MOD(T1.OrdNo,14) + 1)
WHERE T1.OrdDtm < TO_DATE('20230401','YYYYMMDD')
UNION ALL
SELECT T1.OrdNo ,2 OrdDetNo ,T2.ItemId ,1 OrdQty ,T1.OrdDtm
FROM ord_big_tmp T1
INNER JOIN
(
SELECT x.itemId, ROW_NUMBER() OVER(ORDER BY SUBSTR(x.itemID,2,1), X.itemID DESC) ItemNo
FROM Item x
WHERE x.launchdt = TO_DATE('20190101','YYYYMMDD')
) T2 ON (T2.ItemNo = MOD(T1.OrdNo,14) + 1)
WHERE MOD(T1.OrdNo,30) IN (1,2,3,10,12,14,20,25,30)
AND T1.OrdDtm < TO_DATE('20230401','YYYYMMDD')
UNION ALL
SELECT T1.OrdNo ,3 OrdDetNo ,T2.ItemId ,1 OrdQty ,T1.OrdDtm
FROM ord_big_tmp T1
INNER JOIN
(
SELECT x.itemId, ROW_NUMBER() OVER(ORDER BY SUBSTR(x.itemID,3,1), X.itemID ASC) ItemNo
FROM Item x
WHERE x.launchdt = TO_DATE('20190101','YYYYMMDD')
) T2 ON (T2.ItemNo = MOD(T1.OrdNo,14) + 1)
WHERE MOD(T1.OrdNo,40) IN (10,15,30)
AND T1.OrdDtm < TO_DATE('20230401','YYYYMMDD')
)
SELECT T1.OrdNo ,T1.OrdDetNo ,T1.ItemID ,T1.OrdQty ,T2.SalePrc
FROM W01 T1
LEFT OUTER JOIN ItemPrcHist T2
ON (T2.itemId = T1.ItemId
AND T1.OrdDtm >= T2.FromDt
AND T1.OrdDtm < T2.ToDt + interval '1 day')
ORDER BY T1.OrdNo ,T1.OrdDetNo;
-- orddet_big 데이터 생성(20230401 이후)
INSERT INTO orddet_big(OrdNo, OrdDetNo, ItemId, OrdQty, SalePrc)
WITH W01 AS(
SELECT T1.OrdNo ,1 OrdDetNo ,T2.ItemId ,1 OrdQty ,T1.OrdDtm
FROM Ord_big_tmp T1
INNER JOIN
(
SELECT x.itemId, ROW_NUMBER() OVER(ORDER BY x.itemID) ItemNo
FROM Item x
WHERE x.LaunchDt = TO_DATE('20190101','YYYYMMDD')
) T2 ON (T2.ItemNo = MOD(T1.OrdNo,14) + 1)
WHERE T1.OrdDtm >= TO_DATE('20230401','YYYYMMDD')
UNION ALL
SELECT T1.OrdNo ,2 OrdDetNo ,T2.ItemId ,CASE WHEN T1.MemberId LIKE '%9' THEN 2 ELSE 1 END OrdQty ,T1.OrdDtm
FROM Ord_big_tmp T1
INNER JOIN
(
SELECT x.itemId, ROW_NUMBER() OVER(ORDER BY SUBSTR(x.itemID,2,1), X.itemID DESC) ItemNo
FROM Item x
WHERE x.LaunchDt = TO_DATE('20190101','YYYYMMDD')
) T2 ON (T2.ItemNo = MOD(T1.OrdNo,14) + 1)
WHERE MOD(T1.OrdNo,30) IN (1,2,3,10,12,14,20,25,30)
AND T1.OrdDtm >= TO_DATE('20230401','YYYYMMDD')
UNION ALL
SELECT T1.OrdNo ,3 OrdDetNo ,T2.ItemId ,CASE WHEN T1.MemberId LIKE '%9' THEN 2 ELSE 1 END OrdQty ,T1.OrdDtm
FROM Ord_big_tmp T1
INNER JOIN
(
SELECT x.itemId, ROW_NUMBER() OVER(ORDER BY SUBSTR(x.itemID,3,1), X.itemID ASC) ItemNo
FROM Item x
WHERE x.LaunchDt = TO_DATE('20190101','YYYYMMDD')
) T2 ON (T2.ItemNo = MOD(T1.OrdNo,14) + 1)
WHERE MOD(T1.OrdNo,40) IN (10,15,30)
AND T1.OrdDtm >= TO_DATE('20230401','YYYYMMDD')
UNION ALL
SELECT T1.OrdNo ,4 OrdDetNo ,T2.ItemId ,1 OrdQty ,T1.OrdDtm
FROM Ord_big_tmp T1
INNER JOIN
(
SELECT x.itemId, ROW_NUMBER() OVER(ORDER BY SUBSTR(x.itemID,3,1), X.itemID ASC) ItemNo
FROM Item x
WHERE x.LaunchDt = TO_DATE('20230401','YYYYMMDD')
) T2 ON (T2.ItemNo = MOD(T1.OrdNo,6) + 1)
WHERE T1.OrdDtm >= TO_DATE('20230401','YYYYMMDD')
AND (T1.MemberId LIKE '%19' OR T1.MemberId LIKE '%39' OR T1.MemberId LIKE '%49')
UNION ALL
SELECT T1.OrdNo ,5 OrdDetNo ,T2.ItemId ,1 OrdQty ,T1.OrdDtm
FROM Ord_big_tmp T1
INNER JOIN
(
SELECT x.itemId, ROW_NUMBER() OVER(ORDER BY SUBSTR(x.itemID,3,1), X.itemID ASC) ItemNo
FROM Item x
WHERE x.LaunchDt = TO_DATE('20190101','YYYYMMDD')
) T2 ON (T2.ItemNo = MOD(T1.OrdNo,6) + 1)
WHERE T1.OrdDtm >= TO_DATE('20230401','YYYYMMDD')
AND (T1.MemberId LIKE '%19' OR T1.MemberId LIKE '%39')
UNION ALL
SELECT T1.OrdNo ,6 OrdDetNo ,T2.ItemId ,1 OrdQty ,T1.OrdDtm
FROM Ord_big_tmp T1
INNER JOIN
(
SELECT x.itemId, ROW_NUMBER() OVER(ORDER BY SUBSTR(x.itemID,3,1), X.itemID ASC) ItemNo
FROM Item x
WHERE x.LaunchDt = TO_DATE('20190101','YYYYMMDD')
) T2 ON (T2.ItemNo = MOD(T1.OrdNo,9) + 1)
WHERE T1.OrdDtm >= TO_DATE('20230401','YYYYMMDD')
AND (T1.MemberId LIKE '%19' OR T1.MemberId LIKE '%39')
)
SELECT T1.OrdNo ,ROW_NUMBER() OVER(PARTITION BY T1.OrdNo ORDER BY T1.OrdDetNo) OrdDetNo ,T1.ItemID ,T1.OrdQty ,T2.SalePrc
FROM W01 T1
LEFT OUTER JOIN ItemPrcHist T2
ON (T2.itemId = T1.ItemId
AND T1.OrdDtm >= T2.FromDt
AND T1.OrdDtm < T2.ToDt + interval '1 day')
ORDER BY T1.OrdNo ,T1.OrdDetNo;
-- ord_big insert(여기 할 차례)
INSERT INTO ord_big(ordno,orddtm,preparecmpdtm,pickupdtm,memberid,shopid,ordst,ordamt,paytp)
SELECT t1.ordno ,t1.orddtm, t1.preparecmpdtm ,t1.pickupdtm, t1.memberid ,t1.shopid ,t1.ordst
,t2.ordamt ,t1.paytp
FROM ord_big_tmp t1
INNER JOIN (
select x.ordno , sum(x.ordqty * x.saleprc) ordamt
from orddet_big x
group by x.ordno
) t2
on (t1.ordno = t2.ordno)
;
DROP TABLE ord_big_tmp;
ALTER TABLE ord_big ADD CONSTRAINT ord_big_pk PRIMARY KEY(ordno);
ALTER TABLE orddet_big ADD CONSTRAINT orddet_big_pk PRIMARY KEY(ordno);
SQL
복사