DB 별 TPC-H 로드 방법

# 참고 페이지

1. Mysql

https://programmerall.com/article/7807369212/

2. Hive:

https://programmerall.com/article/7360450388/

3. Oracle:

https://infohub.delltechnologies.com/l/design-guide-oracle-big-data-sql-on-dell-emc-powerflex-1/loading-tpch-data-into-the-oracle-database-3  

 

→ 이걸로 함 : https://seanstuber.com/2017/05/27/setting-up-tpc-h-test-data-with-oracle-on-linux/

>  로드(데이터 적재)

- TPC-H 데이터베이스 성능 측정 : https://ycseo.tistory.com/21

- TPC-H #1 . 준비 : https://www.haedongg.net/?p=355

> 확인

- tablespace에 속한 table 조회(owner) :  

  https://oooooohi.tistory.com/entry/tablespace에-속한-table-조회owner

 

- 오라클 테이블 스페이스 사용법(조회, 생성, 삭제)등 총정리 :  

  https://coding-factory.tistory.com/411

- TableSpace 용량 및 관련내용 조회  

https://javafactory.tistory.com/entry/%EC%98%A4%EB%9D%BC%ED%81%B4-%ED%85%8C%EC%9D%B4%EB%B8%94%EC%8A%A4%ED%8E%98%EC%9D%B4%EC%8A%A4-%EC%9A%A9%EB%9F%89-%EC%A1%B0%ED%9A%8C-oracle-tablespace-%EC%9A%A9%EB%9F%89-%EC%A1%B0%ED%9A%8C

4. Sybase:

https://answers.sap.com/questions/11807737/tpc-h-test-tools-for-sap-iq-multiplex.html

## 쿼리 가이드

-- Query 21)
        select s_name, n_name,
        count(*) as numwait
        from supplier, lineitem l1, orders, nation
        where s_suppkey = l1.l_suppkey
        and o_orderkey = l1.l_orderkey
        and o_orderstatus = 'F'
        and l1.l_receiptdate > l1.l_commitdate
        and exists ( select *
        from lineitem l2
        where l2.l_orderkey = l1.l_orderkey
        and l2.l_suppkey <> l1.l_suppkey)
        and s_nationkey = n_nationkey
        and n_name = 'SAUDI ARABIA'
        group by s_name,n_name

     
-- Query 22)     
          select cntrycode,
          count(*) as numcust,
          sum(c_acctbal) as totacctbal
          from ( select substring (c_phone ,1,2) as cntrycode,
          c_acctbal
          from customer
          where substring (c_phone ,1,2) in('13', '31', '23', '29', '30', '18', '17')
          and c_acctbal > ( select avg(c_acctbal)
          from customer
          where c_acctbal > 0.00
          and substring (c_phone ,1,2) in('13','31', '23', '29', '30', '18', '17') )
          and not exists ( select *
          from orders
          where o_custkey = c_custkey) ) as custsale
          group by cntrycode
          order by cntrycode

 

- tbl 만들고 (dbgen -s 10) -> 10G DB 생성

[oracle@oracle-server tpch]$ ls -al
합계 10968908
drwxr-xr-x  2 root root        156  2월 16 15:49 .
drwxrwxr-x+ 6 root root         56  2월 16 15:47 ..
-rw-r--r--  1 root root  244847642  2월 16 15:39 customer.tbl
-rw-r--r--  1 root root 7775727688  2월 16 15:39 lineitem.tbl
-rw-r--r--  1 root root       2224  2월 16 15:39 nation.tbl
-rw-r--r--  1 root root 1749195031  2월 16 15:39 orders.tbl
-rw-r--r--  1 root root  243336157  2월 16 15:39 part.tbl
-rw-r--r--  1 root root 1204850769  2월 16 15:39 partsupp.tbl
-rw-r--r--  1 root root        389  2월 16 15:39 region.tbl
-rw-r--r--  1 root root   14176368  2월 16 15:39 supplier.tbl

[oracle@oracle-server tpch]$ sqlplus tpch/tpch
SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 16 15:52:23 2022
Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

-- CREATE TABLE tpch.ext_part
-- (
--     p_partkey       NUMBER(10, 0),
--     p_name          VARCHAR2(55),
--     p_mfgr          CHAR(25),
--     p_brand         CHAR(10),
--     p_type          VARCHAR2(25),
--     p_size          INTEGER,
--     p_container     CHAR(10),
--     p_retailprice   NUMBER,
--     p_comment       VARCHAR2(23)
-- )
-- ORGANIZATION EXTERNAL
--     (TYPE oracle_loader
--           DEFAULT DIRECTORY tpch_dir
--               ACCESS PARAMETERS (
--                   FIELDS
--                       TERMINATED BY '|'
--                   MISSING FIELD VALUES ARE NULL
--               )
--           LOCATION('part.tbl'));

DROP TABLE tpch.ext_part CASCADE CONSTRAINTS;
DROP TABLE tpch.ext_supplier CASCADE CONSTRAINTS;
DROP TABLE tpch.ext_partsupp CASCADE CONSTRAINTS;
DROP TABLE tpch.ext_customer CASCADE CONSTRAINTS;
DROP TABLE tpch.ext_orders CASCADE CONSTRAINTS;
DROP TABLE tpch.ext_lineitem CASCADE CONSTRAINTS;
DROP TABLE tpch.ext_nation CASCADE CONSTRAINTS;
DROP TABLE tpch.ext_region CASCADE CONSTRAINTS;

DROP TABLE tpch.part CASCADE CONSTRAINTS;
DROP TABLE tpch.supplier CASCADE CONSTRAINTS;
DROP TABLE tpch.partsupp CASCADE CONSTRAINTS;
DROP TABLE tpch.customer CASCADE CONSTRAINTS;
DROP TABLE tpch.orders CASCADE CONSTRAINTS;
DROP TABLE tpch.lineitem CASCADE CONSTRAINTS;
DROP TABLE tpch.nation CASCADE CONSTRAINTS;
DROP TABLE tpch.region CASCADE CONSTRAINTS;


CREATE OR REPLACE DIRECTORY TPCH_DIR AS '/home/oracle/tpch/tpch/TPC-H_Tools_v3.0.0/dbgen/oracle20g';

CREATE TABLE tpch.ext_part
(
    p_partkey       NUMBER(10, 0),
    p_name          VARCHAR2(55),
    p_mfgr          CHAR(25),
    p_brand         CHAR(10),
    p_type          VARCHAR2(25),
    p_size          INTEGER,
    p_container     CHAR(10),
    p_retailprice   NUMBER,
    p_comment       VARCHAR2(23)
)
ORGANIZATION EXTERNAL
    (TYPE oracle_loader
          DEFAULT DIRECTORY tpch_dir
              ACCESS PARAMETERS (
                  FIELDS
                      TERMINATED BY '|'
                  MISSING FIELD VALUES ARE NULL
              )
          LOCATION('part.tbl'));


CREATE TABLE tpch.part
(
    p_partkey       NUMBER(10, 0) NOT NULL,
    p_name          VARCHAR2(55) NOT NULL,
    p_mfgr          CHAR(25) NOT NULL,
    p_brand         CHAR(10) NOT NULL,
    p_type          VARCHAR2(25) NOT NULL,
    p_size          INTEGER NOT NULL,
    p_container     CHAR(10) NOT NULL,
    p_retailprice   NUMBER NOT NULL,
    p_comment       VARCHAR2(23) NOT NULL
);


CREATE TABLE tpch.ext_supplier
(
    s_suppkey     NUMBER(10, 0),
    s_name        CHAR(25),
    s_address     VARCHAR2(40),
    s_nationkey   NUMBER(10, 0),
    s_phone       CHAR(15),
    s_acctbal     NUMBER,
    s_comment     VARCHAR2(101)
)
ORGANIZATION EXTERNAL
    (TYPE oracle_loader
          DEFAULT DIRECTORY tpch_dir
              ACCESS PARAMETERS (
                  FIELDS
                      TERMINATED BY '|'
                  MISSING FIELD VALUES ARE NULL
              )
          LOCATION('supplier.tbl'));


CREATE TABLE tpch.supplier
(
    s_suppkey     NUMBER(10, 0) NOT NULL,
    s_name        CHAR(25) NOT NULL,
    s_address     VARCHAR2(40) NOT NULL,
    s_nationkey   NUMBER(10, 0) NOT NULL,
    s_phone       CHAR(15) NOT NULL,
    s_acctbal     NUMBER NOT NULL,
    s_comment     VARCHAR2(101) NOT NULL
);

CREATE TABLE tpch.ext_partsupp
(
    ps_partkey      NUMBER(10, 0),
    ps_suppkey      NUMBER(10, 0),
    ps_availqty     INTEGER,
    ps_supplycost   NUMBER,
    ps_comment      VARCHAR2(199)
)
ORGANIZATION EXTERNAL
    (TYPE oracle_loader
          DEFAULT DIRECTORY tpch_dir
              ACCESS PARAMETERS (
                  FIELDS
                      TERMINATED BY '|'
                  MISSING FIELD VALUES ARE NULL
              )
          LOCATION('partsupp.tbl'));

CREATE TABLE tpch.partsupp
(
    ps_partkey      NUMBER(10, 0) NOT NULL,
    ps_suppkey      NUMBER(10, 0) NOT NULL,
    ps_availqty     INTEGER NOT NULL,
    ps_supplycost   NUMBER NOT NULL,
    ps_comment      VARCHAR2(199) NOT NULL
);


CREATE TABLE tpch.ext_customer
(
    c_custkey      NUMBER(10, 0),
    c_name         VARCHAR2(25),
    c_address      VARCHAR2(40),
    c_nationkey    NUMBER(10, 0),
    c_phone        CHAR(15),
    c_acctbal      NUMBER,
    c_mktsegment   CHAR(10),
    c_comment      VARCHAR2(117)
)
ORGANIZATION EXTERNAL
    (TYPE oracle_loader
          DEFAULT DIRECTORY tpch_dir
              ACCESS PARAMETERS (
                  FIELDS
                      TERMINATED BY '|'
                  MISSING FIELD VALUES ARE NULL
              )
          LOCATION('customer.tbl'));

CREATE TABLE tpch.customer
(
    c_custkey      NUMBER(10, 0) NOT NULL,
    c_name         VARCHAR2(25) NOT NULL,
    c_address      VARCHAR2(40) NOT NULL,
    c_nationkey    NUMBER(10, 0) NOT NULL,
    c_phone        CHAR(15) NOT NULL,
    c_acctbal      NUMBER NOT NULL,
    c_mktsegment   CHAR(10) NOT NULL,
    c_comment      VARCHAR2(117) NOT NULL
);

CREATE TABLE tpch.ext_orders
(
    o_orderkey        NUMBER(10, 0),
    o_custkey         NUMBER(10, 0),
    o_orderstatus     CHAR(1),
    o_totalprice      NUMBER,
    o_orderdate       CHAR(10),
    o_orderpriority   CHAR(15),
    o_clerk           CHAR(15),
    o_shippriority    INTEGER,
    o_comment         VARCHAR2(79)
)
ORGANIZATION EXTERNAL
    (TYPE oracle_loader
          DEFAULT DIRECTORY tpch_dir
              ACCESS PARAMETERS (
                  FIELDS
                      TERMINATED BY '|'
                  MISSING FIELD VALUES ARE NULL
              )
          LOCATION('orders.tbl'));

CREATE TABLE tpch.orders
(
    o_orderkey        NUMBER(10, 0) NOT NULL,
    o_custkey         NUMBER(10, 0) NOT NULL,
    o_orderstatus     CHAR(1) NOT NULL,
    o_totalprice      NUMBER NOT NULL,
    o_orderdate       DATE NOT NULL,
    o_orderpriority   CHAR(15) NOT NULL,
    o_clerk           CHAR(15) NOT NULL,
    o_shippriority    INTEGER NOT NULL,
    o_comment         VARCHAR2(79) NOT NULL
);

CREATE TABLE tpch.ext_lineitem
(
    l_orderkey        NUMBER(10, 0),
    l_partkey         NUMBER(10, 0),
    l_suppkey         NUMBER(10, 0),
    l_linenumber      INTEGER,
    l_quantity        NUMBER,
    l_extendedprice   NUMBER,
    l_discount        NUMBER,
    l_tax             NUMBER,
    l_returnflag      CHAR(1),
    l_linestatus      CHAR(1),
    l_shipdate        CHAR(10),
    l_commitdate      CHAR(10),
    l_receiptdate     CHAR(10),
    l_shipinstruct    CHAR(25),
    l_shipmode        CHAR(10),
    l_comment         VARCHAR2(44)
)
ORGANIZATION EXTERNAL
    (TYPE oracle_loader
          DEFAULT DIRECTORY tpch_dir
              ACCESS PARAMETERS (
                  FIELDS
                      TERMINATED BY '|'
                  MISSING FIELD VALUES ARE NULL
              )
          LOCATION('lineitem.tbl'));


CREATE TABLE tpch.lineitem
(
    l_orderkey        NUMBER(10, 0),
    l_partkey         NUMBER(10, 0),
    l_suppkey         NUMBER(10, 0),
    l_linenumber      INTEGER,
    l_quantity        NUMBER,
    l_extendedprice   NUMBER,
    l_discount        NUMBER,
    l_tax             NUMBER,
    l_returnflag      CHAR(1),
    l_linestatus      CHAR(1),
    l_shipdate        DATE,
    l_commitdate      DATE,
    l_receiptdate     DATE,
    l_shipinstruct    CHAR(25),
    l_shipmode        CHAR(10),
    l_comment         VARCHAR2(44)
);


CREATE TABLE tpch.ext_nation
(
    n_nationkey   NUMBER(10, 0),
    n_name        CHAR(25),
    n_regionkey   NUMBER(10, 0),
    n_comment     VARCHAR(152)
)
ORGANIZATION EXTERNAL
    (TYPE oracle_loader
          DEFAULT DIRECTORY tpch_dir
              ACCESS PARAMETERS (
                  FIELDS
                      TERMINATED BY '|'
                  MISSING FIELD VALUES ARE NULL
              )
          LOCATION('nation.tbl'));

CREATE TABLE tpch.nation
(
    n_nationkey   NUMBER(10, 0),
    n_name        CHAR(25),
    n_regionkey   NUMBER(10, 0),
    n_comment     VARCHAR(152)
);

CREATE TABLE tpch.ext_region
(
    r_regionkey   NUMBER(10, 0),
    r_name        CHAR(25),
    r_comment     VARCHAR(152)
)
ORGANIZATION EXTERNAL
    (TYPE oracle_loader
          DEFAULT DIRECTORY tpch_dir
              ACCESS PARAMETERS (
                  FIELDS
                      TERMINATED BY '|'
                  MISSING FIELD VALUES ARE NULL
              )
          LOCATION('region.tbl'));

CREATE TABLE tpch.region
(
    r_regionkey   NUMBER(10, 0),
    r_name        CHAR(25),
    r_comment     VARCHAR(152)
);


TRUNCATE TABLE tpch.part;
TRUNCATE TABLE tpch.supplier;
TRUNCATE TABLE tpch.partsupp;
TRUNCATE TABLE tpch.customer;
TRUNCATE TABLE tpch.orders;
TRUNCATE TABLE tpch.lineitem;
TRUNCATE TABLE tpch.nation;
TRUNCATE TABLE tpch.region;

ALTER SESSION SET nls_date_format='YYYY-MM-DD';

INSERT /*+ APPEND */ INTO  tpch.part     SELECT * FROM tpch.ext_part;
INSERT /*+ APPEND */ INTO  tpch.supplier SELECT * FROM tpch.ext_supplier;
INSERT /*+ APPEND */ INTO  tpch.partsupp SELECT * FROM tpch.ext_partsupp;
INSERT /*+ APPEND */ INTO  tpch.customer SELECT * FROM tpch.ext_customer;
INSERT /*+ APPEND */ INTO  tpch.orders   SELECT * FROM tpch.ext_orders;
INSERT /*+ APPEND */ INTO  tpch.lineitem SELECT * FROM tpch.ext_lineitem;
INSERT /*+ APPEND */ INTO  tpch.nation   SELECT * FROM tpch.ext_nation;
INSERT /*+ APPEND */ INTO  tpch.region   SELECT * FROM tpch.ext_region;

--- ORACLE 30G

CREATE TABLE G1.ext_part
(
    p_partkey       NUMBER(10, 0),
    p_name          VARCHAR2(55),
    p_mfgr          CHAR(25),
    p_brand         CHAR(10),
    p_type          VARCHAR2(25),
    p_size          INTEGER,
    p_container     CHAR(10),
    p_retailprice   NUMBER,
    p_comment       VARCHAR2(23)
)
ORGANIZATION EXTERNAL
    (TYPE oracle_loader
          DEFAULT DIRECTORY tpch30_dir
              ACCESS PARAMETERS (
                  FIELDS
                      TERMINATED BY '|'
                  MISSING FIELD VALUES ARE NULL
              )
          LOCATION('part.tbl'));

CREATE TABLE g1.part
(
    p_partkey       NUMBER(10, 0) NOT NULL,
    p_name          VARCHAR2(55) NOT NULL,
    p_mfgr          CHAR(25) NOT NULL,
    p_brand         CHAR(10) NOT NULL,
    p_type          VARCHAR2(25) NOT NULL,
    p_size          INTEGER NOT NULL,
    p_container     CHAR(10) NOT NULL,
    p_retailprice   NUMBER NOT NULL,
    p_comment       VARCHAR2(23) NOT NULL
);


CREATE TABLE g1.ext_supplier
(
    s_suppkey     NUMBER(10, 0),
    s_name        CHAR(25),
    s_address     VARCHAR2(40),
    s_nationkey   NUMBER(10, 0),
    s_phone       CHAR(15),
    s_acctbal     NUMBER,
    s_comment     VARCHAR2(101)
)
ORGANIZATION EXTERNAL
    (TYPE oracle_loader
          DEFAULT DIRECTORY tpch30_dir
              ACCESS PARAMETERS (
                  FIELDS
                      TERMINATED BY '|'
                  MISSING FIELD VALUES ARE NULL
              )
          LOCATION('supplier.tbl'));

CREATE TABLE g1.supplier
(
    s_suppkey     NUMBER(10, 0) NOT NULL,
    s_name        CHAR(25) NOT NULL,
    s_address     VARCHAR2(40) NOT NULL,
    s_nationkey   NUMBER(10, 0) NOT NULL,
    s_phone       CHAR(15) NOT NULL,
    s_acctbal     NUMBER NOT NULL,
    s_comment     VARCHAR2(101) NOT NULL
);

CREATE TABLE g1.ext_partsupp
(
    ps_partkey      NUMBER(10, 0),
    ps_suppkey      NUMBER(10, 0),
    ps_availqty     INTEGER,
    ps_supplycost   NUMBER,
    ps_comment      VARCHAR2(199)
)
ORGANIZATION EXTERNAL
    (TYPE oracle_loader
          DEFAULT DIRECTORY tpch30_dir
              ACCESS PARAMETERS (
                  FIELDS
                      TERMINATED BY '|'
                  MISSING FIELD VALUES ARE NULL
              )
          LOCATION('partsupp.tbl'));

CREATE TABLE g1.partsupp
(
    ps_partkey      NUMBER(10, 0) NOT NULL,
    ps_suppkey      NUMBER(10, 0) NOT NULL,
    ps_availqty     INTEGER NOT NULL,
    ps_supplycost   NUMBER NOT NULL,
    ps_comment      VARCHAR2(199) NOT NULL
);

CREATE TABLE g1.ext_customer
(
    c_custkey      NUMBER(10, 0),
    c_name         VARCHAR2(25),
    c_address      VARCHAR2(40),
    c_nationkey    NUMBER(10, 0),
    c_phone        CHAR(15),
    c_acctbal      NUMBER,
    c_mktsegment   CHAR(10),
    c_comment      VARCHAR2(117)
)
ORGANIZATION EXTERNAL
    (TYPE oracle_loader
          DEFAULT DIRECTORY tpch30_dir
              ACCESS PARAMETERS (
                  FIELDS
                      TERMINATED BY '|'
                  MISSING FIELD VALUES ARE NULL
              )
          LOCATION('customer.tbl'));

CREATE TABLE g1.customer
(
    c_custkey      NUMBER(10, 0) NOT NULL,
    c_name         VARCHAR2(25) NOT NULL,
    c_address      VARCHAR2(40) NOT NULL,
    c_nationkey    NUMBER(10, 0) NOT NULL,
    c_phone        CHAR(15) NOT NULL,
    c_acctbal      NUMBER NOT NULL,
    c_mktsegment   CHAR(10) NOT NULL,
    c_comment      VARCHAR2(117) NOT NULL
);

-- read date values as yyyy-mm-dd text

CREATE TABLE g1.ext_orders
(
    o_orderkey        NUMBER(10, 0),
    o_custkey         NUMBER(10, 0),
    o_orderstatus     CHAR(1),
    o_totalprice      NUMBER,
    o_orderdate       CHAR(10),
    o_orderpriority   CHAR(15),
    o_clerk           CHAR(15),
    o_shippriority    INTEGER,
    o_comment         VARCHAR2(79)
)
ORGANIZATION EXTERNAL
    (TYPE oracle_loader
          DEFAULT DIRECTORY tpch30_dir
              ACCESS PARAMETERS (
                  FIELDS
                      TERMINATED BY '|'
                  MISSING FIELD VALUES ARE NULL
              )
          LOCATION('orders.tbl'));

CREATE TABLE g1.orders
(
    o_orderkey        NUMBER(10, 0) NOT NULL,
    o_custkey         NUMBER(10, 0) NOT NULL,
    o_orderstatus     CHAR(1) NOT NULL,
    o_totalprice      NUMBER NOT NULL,
    o_orderdate       DATE NOT NULL,
    o_orderpriority   CHAR(15) NOT NULL,
    o_clerk           CHAR(15) NOT NULL,
    o_shippriority    INTEGER NOT NULL,
    o_comment         VARCHAR2(79) NOT NULL
);

-- read date values as yyyy-mm-dd text

CREATE TABLE g1.ext_lineitem
(
    l_orderkey        NUMBER(10, 0),
    l_partkey         NUMBER(10, 0),
    l_suppkey         NUMBER(10, 0),
    l_linenumber      INTEGER,
    l_quantity        NUMBER,
    l_extendedprice   NUMBER,
    l_discount        NUMBER,
    l_tax             NUMBER,
    l_returnflag      CHAR(1),
    l_linestatus      CHAR(1),
    l_shipdate        CHAR(10),
    l_commitdate      CHAR(10),
    l_receiptdate     CHAR(10),
    l_shipinstruct    CHAR(25),
    l_shipmode        CHAR(10),
    l_comment         VARCHAR2(44)
)
ORGANIZATION EXTERNAL
    (TYPE oracle_loader
          DEFAULT DIRECTORY tpch30_dir
              ACCESS PARAMETERS (
                  FIELDS
                      TERMINATED BY '|'
                  MISSING FIELD VALUES ARE NULL
              )
          LOCATION('lineitem.tbl'));

CREATE TABLE g1.lineitem
(
    l_orderkey        NUMBER(10, 0),
    l_partkey         NUMBER(10, 0),
    l_suppkey         NUMBER(10, 0),
    l_linenumber      INTEGER,
    l_quantity        NUMBER,
    l_extendedprice   NUMBER,
    l_discount        NUMBER,
    l_tax             NUMBER,
    l_returnflag      CHAR(1),
    l_linestatus      CHAR(1),
    l_shipdate        DATE,
    l_commitdate      DATE,
    l_receiptdate     DATE,
    l_shipinstruct    CHAR(25),
    l_shipmode        CHAR(10),
    l_comment         VARCHAR2(44)
);

CREATE TABLE g1.ext_nation
(
    n_nationkey   NUMBER(10, 0),
    n_name        CHAR(25),
    n_regionkey   NUMBER(10, 0),
    n_comment     VARCHAR(152)
)
ORGANIZATION EXTERNAL
    (TYPE oracle_loader
          DEFAULT DIRECTORY tpch30_dir
              ACCESS PARAMETERS (
                  FIELDS
                      TERMINATED BY '|'
                  MISSING FIELD VALUES ARE NULL
              )
          LOCATION('nation.tbl'));

CREATE TABLE g1.nation
(
    n_nationkey   NUMBER(10, 0),
    n_name        CHAR(25),
    n_regionkey   NUMBER(10, 0),
    n_comment     VARCHAR(152)
);

CREATE TABLE g1.ext_region
(
    r_regionkey   NUMBER(10, 0),
    r_name        CHAR(25),
    r_comment     VARCHAR(152)
)
ORGANIZATION EXTERNAL
    (TYPE oracle_loader
          DEFAULT DIRECTORY tpch30_dir
              ACCESS PARAMETERS (
                  FIELDS
                      TERMINATED BY '|'
                  MISSING FIELD VALUES ARE NULL
              )
          LOCATION('region.tbl'));

CREATE TABLE g1.region
(
    r_regionkey   NUMBER(10, 0),
    r_name        CHAR(25),
    r_comment     VARCHAR(152)
);


TRUNCATE TABLE g1.part;
TRUNCATE TABLE g1.supplier;
TRUNCATE TABLE g1.partsupp;
TRUNCATE TABLE g1.customer;
TRUNCATE TABLE g1.orders;
TRUNCATE TABLE g1.lineitem;
TRUNCATE TABLE g1.nation;
TRUNCATE TABLE g1.region;

ALTER SESSION SET nls_date_format='YYYY-MM-DD';

INSERT /*+ APPEND */ INTO  g1.part     SELECT * FROM g1.ext_part;
INSERT /*+ APPEND */ INTO  g1.supplier SELECT * FROM g1.ext_supplier;
INSERT /*+ APPEND */ INTO  g1.partsupp SELECT * FROM g1.ext_partsupp;
INSERT /*+ APPEND */ INTO  g1.customer SELECT * FROM g1.ext_customer;
INSERT /*+ APPEND */ INTO  g1.orders   SELECT * FROM g1.ext_orders;
INSERT /*+ APPEND */ INTO  g1.lineitem SELECT * FROM g1.ext_lineitem;
INSERT /*+ APPEND */ INTO  g1.nation   SELECT * FROM g1.ext_nation;
INSERT /*+ APPEND */ INTO  g1.region   SELECT * FROM g1.ext_region;

--- (만약의 상황) 데이터를 다시 넣기

ALTER TABLE tpch20.lineitem drop CONSTRAINT chk_lineitem_ship_rcpt ----check(l_shipdate <= l_receiptdate);
;
ALTER TABLE tpch20.lineitem
DROP CONSTRAINT chk_lineitem_discount -- --check(l_discount >= 0.00 AND l_discount <= 1.00);
;
ALTER TABLE tpch20.lineitem
    drop CONSTRAINT chk_lineitem_tax ----check(l_tax >= 0);
    ;
ALTER TABLE tpch20.lineitem
    drop CONSTRAINT chk_lineitem_extendedprice ----check(l_extendedprice >= 0);
;
ALTER TABLE tpch20.lineitem
    drop CONSTRAINT chk_lineitem_quantity --check(l_quantity >= 0);
;
ALTER TABLE tpch20.orders
    drop CONSTRAINT chk_orders_totalprice --check(o_totalprice >= 0);
;
ALTER TABLE tpch20.partsupp
    drop CONSTRAINT chk_partsupp_supplycost --check(ps_supplycost >= 0);
;
ALTER TABLE tpch20.partsupp
    drop CONSTRAINT chk_partsupp_availqty --check(ps_availqty >= 0);
;
ALTER TABLE tpch20.part
    drop CONSTRAINT chk_part_retailprice --check(p_retailprice >= 0);
;
    ALTER TABLE tpch20.part
    drop CONSTRAINT chk_part_size --check(p_size >= 0);
;

ALTER TABLE tpch20.nation
    drop CONSTRAINT chk_nation_nationkey --check(n_nationkey >= 0);
 ;   
ALTER TABLE tpch20.region
    drop CONSTRAINT chk_region_regionkey --check(r_regionkey >= 0);
;
ALTER TABLE tpch20.partsupp
    drop CONSTRAINT chk_partsupp_partkey --check(ps_partkey >= 0);
;
ALTER TABLE tpch20.customer
    drop CONSTRAINT chk_customer_custkey --check(c_custkey >= 0);
;
ALTER TABLE tpch20.supplier
    drop CONSTRAINT chk_supplier_suppkey --check(s_suppkey >= 0);
;
    ALTER TABLE tpch20.part
    drop CONSTRAINT chk_part_partkey --check(p_partkey >= 0);
;

 ALTER TABLE tpch20.region
    drop CONSTRAINT pk_region -- --primary key(r_regionkey);
;
 ALTER TABLE tpch20.nation
    drop CONSTRAINT pk_nation --primary key(n_nationkey);
;
ALTER TABLE tpch20.lineitem
    drop CONSTRAINT pk_lineitem --primary key(l_linenumber, l_orderkey);
;
ALTER TABLE tpch20.orders
    drop CONSTRAINT pk_orders --primary key(o_orderkey);
;
ALTER TABLE tpch20.customer
    drop CONSTRAINT pk_customer --primary key(c_custkey);
;
ALTER TABLE tpch20.partsupp
    drop CONSTRAINT pk_partsupp --primary key(ps_partkey, ps_suppkey);
;
ALTER TABLE tpch20.supplier
    drop CONSTRAINT pk_supplier --primary key(s_suppkey);
;
ALTER TABLE tpch20.part
    drop CONSTRAINT pk_part --primary key(p_partkey);
;


-- 1.4.2.3


ALTER TABLE tpch20.lineitem
    drop CONSTRAINT fk_lineitem_partsupp; -- FOREIGN KEY(l_partkey, l_suppkey);
--        REFERENCES tpch20.partsupp(ps_partkey, ps_suppkey);
 ;
ALTER TABLE tpch20.lineitem
    drop CONSTRAINT fk_lineitem_supplier ;--FOREIGN KEY(l_suppkey) REFERENCES tpch20.supplier(s_suppkey);
;
ALTER TABLE tpch20.lineitem
    drop CONSTRAINT fk_lineitem_part ; -- FOREIGN KEY(l_partkey) REFERENCES tpch20.part(p_partkey);
 ;
ALTER TABLE tpch20.lineitem
    drop CONSTRAINT fk_lineitem_order ; --FOREIGN KEY(l_orderkey) REFERENCES tpch20.orders(o_orderkey);
;
ALTER TABLE tpch20.orders
    drop CONSTRAINT fk_orders_customer -- FOREIGN KEY(o_custkey) REFERENCES tpch20.customer(c_custkey);
  ;  
ALTER TABLE tpch20.customer
    drop CONSTRAINT fk_customer_nation -- FOREIGN KEY(c_nationkey) REFERENCES tpch20.nation(n_nationkey);
 ;   
ALTER TABLE tpch20.partsupp
    drop CONSTRAINT fk_partsupp_supplier -- FOREIGN KEY(ps_suppkey) REFERENCES tpch20.supplier(s_suppkey);
;
         ALTER TABLE tpch20.partsupp
    drop CONSTRAINT fk_partsupp_part -- FOREIGN KEY(ps_partkey) REFERENCES tpch20.part(p_partkey);
; 
 
 -----

INSERT /*+ APPEND */ INTO  tpch20.region   SELECT * FROM tpch20.ext_region;
INSERT /*+ APPEND */ INTO  tpch20.customer SELECT * FROM tpch20.ext_customer;


ALTER TABLE tpch20.part
    ADD CONSTRAINT pk_part PRIMARY KEY(p_partkey);

ALTER TABLE tpch20.supplier
    ADD CONSTRAINT pk_supplier PRIMARY KEY(s_suppkey);

ALTER TABLE tpch20.partsupp
    ADD CONSTRAINT pk_partsupp PRIMARY KEY(ps_partkey, ps_suppkey);



ALTER TABLE tpch20.customer
    ADD CONSTRAINT pk_customer PRIMARY KEY(c_custkey);

ALTER TABLE tpch20.orders
    ADD CONSTRAINT pk_orders PRIMARY KEY(o_orderkey);

ALTER TABLE tpch20.lineitem
    ADD CONSTRAINT pk_lineitem PRIMARY KEY(l_linenumber, l_orderkey);

ALTER TABLE tpch20.nation
    ADD CONSTRAINT pk_nation PRIMARY KEY(n_nationkey);

ALTER TABLE tpch20.region
    ADD CONSTRAINT pk_region PRIMARY KEY(r_regionkey);

 
 ALTER TABLE tpch20.partsupp
    ADD CONSTRAINT fk_partsupp_part FOREIGN KEY(ps_partkey) REFERENCES tpch20.part(p_partkey);

ALTER TABLE tpch20.partsupp
    ADD CONSTRAINT fk_partsupp_supplier FOREIGN KEY(ps_suppkey) REFERENCES tpch20.supplier(s_suppkey);

ALTER TABLE tpch20.customer
    ADD CONSTRAINT fk_customer_nation FOREIGN KEY(c_nationkey) REFERENCES tpch20.nation(n_nationkey);

ALTER TABLE tpch20.orders
    ADD CONSTRAINT fk_orders_customer FOREIGN KEY(o_custkey) REFERENCES tpch20.customer(c_custkey);

ALTER TABLE tpch20.lineitem
    ADD CONSTRAINT fk_lineitem_order FOREIGN KEY(l_orderkey) REFERENCES tpch20.orders(o_orderkey);

ALTER TABLE tpch20.lineitem
    ADD CONSTRAINT fk_lineitem_part FOREIGN KEY(l_partkey) REFERENCES tpch20.part(p_partkey);

ALTER TABLE tpch20.lineitem
    ADD CONSTRAINT fk_lineitem_supplier FOREIGN KEY(l_suppkey) REFERENCES tpch20.supplier(s_suppkey);

ALTER TABLE tpch20.lineitem
    ADD CONSTRAINT fk_lineitem_partsupp FOREIGN KEY(l_partkey, l_suppkey)
        REFERENCES tpch20.partsupp(ps_partkey, ps_suppkey);

-- 1.4.2.4 - 1

ALTER TABLE tpch20.part
    ADD CONSTRAINT chk_part_partkey CHECK(p_partkey >= 0);

ALTER TABLE tpch20.supplier
    ADD CONSTRAINT chk_supplier_suppkey CHECK(s_suppkey >= 0);

ALTER TABLE tpch20.customer
    ADD CONSTRAINT chk_customer_custkey CHECK(c_custkey >= 0);

ALTER TABLE tpch20.partsupp
    ADD CONSTRAINT chk_partsupp_partkey CHECK(ps_partkey >= 0);

ALTER TABLE tpch20.region
    ADD CONSTRAINT chk_region_regionkey CHECK(r_regionkey >= 0);

ALTER TABLE tpch20.nation
    ADD CONSTRAINT chk_nation_nationkey CHECK(n_nationkey >= 0);

-- 1.4.2.4 - 2

ALTER TABLE tpch20.part
    ADD CONSTRAINT chk_part_size CHECK(p_size >= 0);

ALTER TABLE tpch20.part
    ADD CONSTRAINT chk_part_retailprice CHECK(p_retailprice >= 0);

ALTER TABLE tpch20.partsupp
    ADD CONSTRAINT chk_partsupp_availqty CHECK(ps_availqty >= 0);

ALTER TABLE tpch20.partsupp
    ADD CONSTRAINT chk_partsupp_supplycost CHECK(ps_supplycost >= 0);

ALTER TABLE tpch20.orders
    ADD CONSTRAINT chk_orders_totalprice CHECK(o_totalprice >= 0);

ALTER TABLE tpch20.lineitem
    ADD CONSTRAINT chk_lineitem_quantity CHECK(l_quantity >= 0);

ALTER TABLE tpch20.lineitem
    ADD CONSTRAINT chk_lineitem_extendedprice CHECK(l_extendedprice >= 0);

ALTER TABLE tpch20.lineitem
    ADD CONSTRAINT chk_lineitem_tax CHECK(l_tax >= 0);

-- 1.4.2.4 - 3

ALTER TABLE tpch20.lineitem
    ADD CONSTRAINT chk_lineitem_discount CHECK(l_discount >= 0.00 AND l_discount <= 1.00);

-- 1.4.2.4 - 4

ALTER TABLE tpch20.lineitem
    ADD CONSTRAINT chk_lineitem_ship_rcpt CHECK(l_shipdate <= l_receiptdate);