튜닝 연습용 테이블 생성하기

•

PG startdb 에 접속해 아래 SQL들을 순차적으로 실행해 튜닝 테스트용 테이블을 생성합니다.

-- tr_ord_big_tmp와 ord_big 테이블 생성
CREATE TABLE tr_ord_big_tmp 
(           ord_no    decimal   (20,0)    NOT NULL 
          ,ord_dtm    timestamp       NULL 
          ,prepare_cmp_dtm    timestamp       NULL 
          ,pickup_dtm    timestamp       NULL 
          ,member_id    varchar   (40)    NULL 
          ,shop_id    varchar   (40)    NULL 
          ,ord_st    varchar   (40)    NULL 
          ,ord_atm    decimal   (18,3)    NULL 
          ,pay_tp    varchar   (40)    NULL 
      );
      
CREATE TABLE tr_ord_big 
(ord_no    decimal   (20,0)    NOT NULL 
          ,ord_dtm    timestamp       NULL 
          ,prepare_cmp_dtm    timestamp       NULL 
          ,pickup_dtm    timestamp       NULL 
          ,member_id    varchar   (40)    NULL 
          ,shop_id    varchar   (40)    NULL 
          ,ord_st    varchar   (40)    NULL 
          ,ord_atm    decimal   (18,3)    NULL 
          ,pay_tp    varchar   (40)    NULL 
      );
      

-- tr_ord_big_tmp 데이터 생성 20,795,823 건 생성(나중에 DROP)
INSERT INTO tr_ord_big_tmp 
    (ord_no ,ord_dtm, prepare_cmp_dtm, pickup_dtm, member_id, shop_id, ord_st, ord_atm, pay_tp)
SELECT  row_number() OVER(ORDER BY t3.new_ord_dtm, t3.new_shop_id,t3.new_member_id) new_ord_no
        ,new_ord_dtm
        ,new_prepare_cmp_dtm
        ,new_pickup_dtm
        ,new_member_id
        ,new_shop_id
        ,ord_st
        ,null ord_atm
        ,pay_tp
FROM    (
        SELECT  t1.ord_dtm + (t2.n || ' second')::interval new_ord_dtm
                ,t1.prepare_cmp_dtm + (t2.n || ' second')::interval new_prepare_cmp_dtm
                ,t1.pickup_dtm + (t2.n || ' second')::interval new_pickup_dtm
                ,'M' || 
                LPAD(
                    (CASE WHEN (substring(t1.member_id,2,4)::integer + T2.n) >= 9999 THEN 9999
                          ELSE (substring(t1.member_id,2,4)::integer + T2.n) END)::varchar, 4, '0') new_member_id
                ,'S' || 
                LPAD(
                    (CASE WHEN (substring(t1.shop_id,2,3)::integer + T2.n) >= 300 THEN 300
                          ELSE (substring(t1.shop_id,2,3)::integer + T2.n) END)::varchar, 3, '0') new_shop_id
                ,t1.shop_id
                ,t1.ord_st
                ,NULL ord_atm
                ,t1.pay_tp
        FROM    tr_ord t1
                CROSS JOIN (
                    select generate_series(0, 30) as n
                    ) t2
        ) T3;
        
-- tr_ord_det_big 생성
CREATE TABLE 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 );
          
          
          

-- tr_ord_det_big 데이터 생성(20230401 이전)
INSERT INTO tr_ord_det_big(ord_no, ord_det_no, item_id, ord_qty, sale_prc)
WITH W01 AS(
SELECT  T1.ord_no ,1 ord_det_no ,T2.item_id ,1 ord_qty ,T1.ord_dtm
FROM    tr_ord_big_tmp T1
        INNER JOIN
            (
            SELECT  x.item_id, ROW_NUMBER() OVER(ORDER BY x.item_id) item_no
            FROM    ms_item x
            WHERE   x.launch_dt = TO_DATE('20190101','YYYYMMDD')
            ) T2 ON (T2.item_no = MOD(T1.ord_no,14) + 1)
WHERE     T1.ord_dtm < TO_DATE('20230401','YYYYMMDD')
UNION ALL
SELECT  T1.ord_no ,2 ord_det_no ,T2.item_id ,1 ord_qty ,T1.ord_dtm
FROM    tr_ord_big_tmp T1
        INNER JOIN
            (
            SELECT  x.item_id, ROW_NUMBER() OVER(ORDER BY SUBSTR(x.item_id,2,1), x.item_id DESC) item_no
            FROM    ms_item x
            WHERE   x.launch_dt = TO_DATE('20190101','YYYYMMDD')
            ) T2 ON (T2.item_no = MOD(T1.ord_no,14) + 1)
WHERE   MOD(T1.ord_no,30) IN (1,2,3,10,12,14,20,25,30)
AND     T1.ord_dtm < TO_DATE('20230401','YYYYMMDD')
UNION ALL
SELECT  T1.ord_no ,3 ord_det_no ,T2.item_id ,1 ord_qty ,T1.ord_dtm
FROM    tr_ord_big_tmp T1
        INNER JOIN
            (
            SELECT  x.item_id, ROW_NUMBER() OVER(ORDER BY SUBSTR(x.item_id,3,1), x.item_id ASC) item_no
            FROM    ms_item x
            WHERE   x.launch_dt = TO_DATE('20190101','YYYYMMDD')
            ) T2 ON (T2.item_no = MOD(T1.ord_no,14) + 1)
WHERE   MOD(T1.ord_no,40) IN (10,15,30)
AND     T1.ord_dtm < TO_DATE('20230401','YYYYMMDD')
)
SELECT  T1.ord_no ,T1.ord_det_no ,T1.item_id ,T1.ord_qty ,T2.sale_prc
FROM    W01 T1
        LEFT OUTER JOIN ms_item_prc_hist T2
            ON (T2.item_id = T1.item_id
                AND T1.ord_dtm >= T2.from_dt
                AND T1.ord_dtm < T2.to_dt + interval '1 day')
ORDER BY T1.ord_no ,T1.ord_det_no;



-- tr_ord_det_big 데이터 생성(20230401 이후)
INSERT INTO tr_ord_det_big(ord_no, ord_det_no, item_id, ord_qty, sale_prc)
WITH W01 AS(
SELECT  T1.ord_no ,1 ord_det_no ,T2.item_id ,1 ord_qty ,T1.ord_dtm
FROM    tr_ord_big_tmp T1
        INNER JOIN
            (
            SELECT  x.item_id, ROW_NUMBER() OVER(ORDER BY x.item_id) item_no
            FROM    ms_item x
            WHERE   x.launch_dt = TO_DATE('20190101','YYYYMMDD')
            ) T2 ON (T2.item_no = MOD(T1.ord_no,14) + 1)
WHERE   T1.ord_dtm >= TO_DATE('20230401','YYYYMMDD')
UNION ALL
SELECT  T1.ord_no ,2 ord_det_no ,T2.item_id ,CASE WHEN t1.member_id LIKE '%9' THEN 2 ELSE 1 END ord_qty ,T1.ord_dtm
FROM    tr_ord_big_tmp T1
        INNER JOIN
            (
            SELECT  x.item_id, ROW_NUMBER() OVER(ORDER BY SUBSTR(x.item_id,2,1), x.item_id DESC) item_no
            FROM    ms_item x
            WHERE   x.launch_dt = TO_DATE('20190101','YYYYMMDD')
            ) T2 ON (T2.item_no = MOD(T1.ord_no,14) + 1)
WHERE   MOD(T1.ord_no,30) IN (1,2,3,10,12,14,20,25,30)
AND     T1.ord_dtm >= TO_DATE('20230401','YYYYMMDD')
UNION ALL
SELECT  T1.ord_no ,3 ord_det_no ,T2.item_id ,CASE WHEN t1.member_id LIKE '%9' THEN 2 ELSE 1 END ord_qty ,T1.ord_dtm
FROM    tr_ord_big_tmp T1
        INNER JOIN
            (
            SELECT  x.item_id, ROW_NUMBER() OVER(ORDER BY SUBSTR(x.item_id,3,1), x.item_id ASC) item_no
            FROM    ms_item x
            WHERE   x.launch_dt = TO_DATE('20190101','YYYYMMDD')
            ) T2 ON (T2.item_no = MOD(T1.ord_no,14) + 1)
WHERE   MOD(T1.ord_no,40) IN (10,15,30)
AND     T1.ord_dtm >= TO_DATE('20230401','YYYYMMDD')
UNION ALL
SELECT  T1.ord_no ,4 ord_det_no ,T2.item_id ,1 ord_qty ,T1.ord_dtm
FROM    tr_ord_big_tmp T1
        INNER JOIN
            (
            SELECT  x.item_id, ROW_NUMBER() OVER(ORDER BY SUBSTR(x.item_id,3,1), x.item_id ASC) item_no
            FROM    ms_item x
            WHERE   x.launch_dt = TO_DATE('20230401','YYYYMMDD')
            ) T2 ON (T2.item_no = MOD(T1.ord_no,6) + 1)
WHERE   T1.ord_dtm >= TO_DATE('20230401','YYYYMMDD')
AND     (t1.member_id LIKE '%19' OR t1.member_id LIKE '%39' OR t1.member_id LIKE '%49')
UNION ALL
SELECT  T1.ord_no ,5 ord_det_no ,T2.item_id ,1 ord_qty ,T1.ord_dtm
FROM    tr_ord_big_tmp T1
        INNER JOIN
            (
            SELECT  x.item_id, ROW_NUMBER() OVER(ORDER BY SUBSTR(x.item_id,3,1), x.item_id ASC) item_no
            FROM    ms_item x
            WHERE   x.launch_dt = TO_DATE('20190101','YYYYMMDD')
            ) T2 ON (T2.item_no = MOD(T1.ord_no,6) + 1)
WHERE   T1.ord_dtm >= TO_DATE('20230401','YYYYMMDD')
AND     (t1.member_id LIKE '%19' OR t1.member_id LIKE '%39')
UNION ALL
SELECT  T1.ord_no ,6 ord_det_no ,T2.item_id ,1 ord_qty ,T1.ord_dtm
FROM    tr_ord_big_tmp T1
        INNER JOIN
            (
            SELECT  x.item_id, ROW_NUMBER() OVER(ORDER BY SUBSTR(x.item_id,3,1), x.item_id ASC) item_no
            FROM    ms_item x
            WHERE   x.launch_dt = TO_DATE('20190101','YYYYMMDD')
            ) T2 ON (T2.item_no = MOD(T1.ord_no,9) + 1)
WHERE   T1.ord_dtm >= TO_DATE('20230401','YYYYMMDD')
AND     (t1.member_id LIKE '%19' OR t1.member_id LIKE '%39')
)
SELECT  T1.ord_no ,ROW_NUMBER() OVER(PARTITION BY T1.ord_no ORDER BY T1.ord_det_no) ord_det_no ,T1.item_id ,T1.ord_qty ,T2.sale_prc
FROM    W01 T1
        LEFT OUTER JOIN ms_item_prc_hist T2
            ON (T2.item_id = T1.item_id
                AND T1.ord_dtm >= T2.from_dt
                AND T1.ord_dtm < T2.to_dt + interval '1 day')
ORDER BY T1.ord_no ,T1.ord_det_no;


-- ord_big insert(여기 할 차례)
INSERT INTO tr_ord_big(ord_no,ord_dtm,prepare_cmp_dtm,pickup_dtm,member_id,shop_id,ord_st,ord_atm,pay_tp)
SELECT  t1.ord_no ,t1.ord_dtm, t1.prepare_cmp_dtm ,t1.pickup_dtm, t1.member_id ,t1.shop_id ,t1.ord_st
        ,t2.ord_atm ,t1.pay_tp
FROM    tr_ord_big_tmp t1
        INNER JOIN (
            select x.ord_no , sum(x.ord_qty * x.sale_prc) ord_atm
            from   tr_ord_det_big x
            group by x.ord_no
        ) t2
        on (t1.ord_no = t2.ord_no)
        ;
        
DROP TABLE tr_ord_big_tmp;
SQL
복사