|| Oracle 11gR2: I/O Performance Tuning Series, Listing 4
||
|| Contains:
|| -- SQL statements for creation of TPC-H infrastructure
|| -- SQL statements from result of TPC-H table and index creation
|| -- XML configuration files for control of object creation
||
|| that demonstrate the result of using Quest’s Benchmark Factory to create
|| and load a representative TPC-H schema for eventual demonstration of
|| performance tuning methods for I/O subsystems.
||
|| Author: Jim Czuprynski
||
*/

/* 
|| Listing 4.1:
|| Creating schema owner and tablespaces to support TPC-H objects:
|| - All tablespaces use the default (8KB) database block size, are locally-managed, and 
||   use ASSM for segment space management
|| - Redo logging is deactivated by default (for initial loading)
|| - Reference tablespaces are defined as SMALLFILE tablespaces with AUTOALLOCATE extents||
*/

-----
-- Tablespace:  TPCH_REF_*
-- Contents:    TPC-H Reference Data and Indexes
-----
DROP TABLESPACE tpch_ref_data INCLUDING CONTENTS AND DATAFILES;
CREATE SMALLFILE TABLESPACE tpch_ref_data
    DATAFILE '/u01/app/oradata/TPCH/tpch_ref_data01.dbf' 
    SIZE 1M REUSE
    AUTOEXTEND ON
    MAXSIZE UNLIMITED
    PERMANENT
    NOLOGGING
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE
    SEGMENT SPACE MANAGEMENT AUTO
;

DROP TABLESPACE tpch_ref_idx INCLUDING CONTENTS AND DATAFILES;
CREATE SMALLFILE TABLESPACE tpch_ref_idx
    DATAFILE '/u01/app/oradata/TPCH/tpch_ref_idx01.dbf' 
    SIZE 1M REUSE
    AUTOEXTEND ON
    MAXSIZE UNLIMITED
    PERMANENT
    NOLOGGING
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE
    SEGMENT SPACE MANAGEMENT AUTO
;

/*
|| Create new user account for TPC-H testing, using the reference
|| tablespace as the default tablespace for object creation
*/

DROP USER tpch CASCADE;
CREATE USER tpch
    IDENTIFIED BY tpch
    DEFAULT TABLESPACE tpch_ref_data
    TEMPORARY TABLESPACE temp
    PROFILE DEFAULT
;

GRANT RESOURCE TO tpch;
GRANT CREATE PROCEDURE TO tpch;
GRANT CREATE PUBLIC SYNONYM TO tpch;
GRANT DROP PUBLIC SYNONYM TO tpch;
GRANT CREATE SEQUENCE TO tpch;
GRANT CREATE SESSION TO tpch;
GRANT CREATE SYNONYM TO tpch;
GRANT CREATE TABLE TO tpch;
GRANT EXECUTE ANY PROCEDURE TO tpch;
GRANT UNLIMITED TABLESPACE TO tpch;

/*
|| Create remainder of tablespaces to support TPC-H objects:
|| - All tablespaces use the default (8KB) database block size, are locally-managed, and 
||   use ASSM for segment space management
|| - Redo logging is activated by default
|| - Transaction tablespaces are defined as BIGFILE tablespaces with UNIFORM extents
*/

-----
-- Tablespace:  TPCH_PART_*
-- Contents:    TPC-H Parts Data and Indexes
-----
DROP TABLESPACE tpch_part_data INCLUDING CONTENTS AND DATAFILES;
CREATE BIGFILE TABLESPACE tpch_part_data
    DATAFILE '/u01/app/oradata/TPCH/tpch_part_data01.dbf' 
    SIZE 10M REUSE
    AUTOEXTEND ON
    MAXSIZE UNLIMITED
    PERMANENT
    NOLOGGING
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
    SEGMENT SPACE MANAGEMENT AUTO
;

DROP TABLESPACE tpch_part_idx INCLUDING CONTENTS AND DATAFILES;
CREATE BIGFILE TABLESPACE tpch_part_idx
    DATAFILE '/u01/app/oradata/TPCH/tpch_part_idx01.dbf' 
    SIZE 10M REUSE
    AUTOEXTEND ON
    MAXSIZE UNLIMITED
    PERMANENT
    NOLOGGING
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
    SEGMENT SPACE MANAGEMENT AUTO
;

-----
-- Tablespace:  TPCH_SUPPLIER_*
-- Contents:    TPC-H Suppliers Data and Indexes
-----
DROP TABLESPACE tpch_supplier_data INCLUDING CONTENTS AND DATAFILES;
CREATE BIGFILE TABLESPACE tpch_supplier_data
    DATAFILE '/u01/app/oradata/TPCH/tpch_supplier_data01.dbf' 
    SIZE 10M REUSE
    AUTOEXTEND ON
    MAXSIZE UNLIMITED
    PERMANENT
    NOLOGGING
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
    SEGMENT SPACE MANAGEMENT AUTO
;

DROP TABLESPACE tpch_supplier_idx INCLUDING CONTENTS AND DATAFILES;
CREATE BIGFILE TABLESPACE tpch_supplier_idx
    DATAFILE '/u01/app/oradata/TPCH/tpch_supplier_idx01.dbf' 
    SIZE 10M REUSE
    AUTOEXTEND ON
    MAXSIZE UNLIMITED
    PERMANENT
    NOLOGGING
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
    SEGMENT SPACE MANAGEMENT AUTO
;

-----
-- Tablespace:  TPCH_PARTSUPP_*
-- Contents:    TPC-H Part-Supplier Association Data and Indexes
-----
DROP TABLESPACE tpch_partsupp_data INCLUDING CONTENTS AND DATAFILES;
CREATE BIGFILE TABLESPACE tpch_partsupp_data
    DATAFILE '/u01/app/oradata/TPCH/tpch_partsupp_data01.dbf' 
    SIZE 10M REUSE
    AUTOEXTEND ON
    MAXSIZE UNLIMITED
    PERMANENT
    NOLOGGING
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
    SEGMENT SPACE MANAGEMENT AUTO
;

DROP TABLESPACE tpch_partsupp_idx INCLUDING CONTENTS AND DATAFILES;
CREATE BIGFILE TABLESPACE tpch_partsupp_idx
    DATAFILE '/u01/app/oradata/TPCH/tpch_partsupp_idx01.dbf' 
    SIZE 10M REUSE
    AUTOEXTEND ON
    MAXSIZE UNLIMITED
    PERMANENT
    NOLOGGING
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
    SEGMENT SPACE MANAGEMENT AUTO
;

-----
-- Tablespace:  TPCH_CUSTOMER_*
-- Contents:    TPC-H Customer Data and Indexes
-----

DROP TABLESPACE tpch_customer_data INCLUDING CONTENTS AND DATAFILES;
CREATE BIGFILE TABLESPACE tpch_customer_data
    DATAFILE '/u01/app/oradata/TPCH/tpch_customer_data01.dbf' 
    SIZE 10M REUSE
    AUTOEXTEND ON
    MAXSIZE UNLIMITED
    PERMANENT
    NOLOGGING
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
    SEGMENT SPACE MANAGEMENT AUTO
;

DROP TABLESPACE tpch_customer_idx INCLUDING CONTENTS AND DATAFILES;
CREATE BIGFILE TABLESPACE tpch_customer_idx
    DATAFILE '/u01/app/oradata/TPCH/tpch_customer_idx01.dbf' 
    SIZE 10M REUSE
    AUTOEXTEND ON
    MAXSIZE UNLIMITED
    PERMANENT
    NOLOGGING
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
    SEGMENT SPACE MANAGEMENT AUTO
;

-----
-- Tablespace:  TPCH_ORDER_*
-- Contents:    TPC-H Orders Data and Indexes
-----
DROP TABLESPACE tpch_order_data INCLUDING CONTENTS AND DATAFILES;
CREATE BIGFILE TABLESPACE tpch_order_data
    DATAFILE '/u01/app/oradata/TPCH/tpch_order_data01.dbf' 
    SIZE 10M REUSE
    AUTOEXTEND ON
    MAXSIZE UNLIMITED
    PERMANENT
    NOLOGGING
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
    SEGMENT SPACE MANAGEMENT AUTO
;

DROP TABLESPACE tpch_order_idx INCLUDING CONTENTS AND DATAFILES;
CREATE BIGFILE TABLESPACE tpch_order_idx
    DATAFILE '/u01/app/oradata/TPCH/tpch_order_idx01.dbf' 
    SIZE 10M REUSE
    AUTOEXTEND ON
    MAXSIZE UNLIMITED
    PERMANENT
    NOLOGGING
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
    SEGMENT SPACE MANAGEMENT AUTO
;

-----
-- Tablespace:  TPCH_LINEITEM_*
-- Contents:    TPC-H Order Line Items Data and Indexes
-----
DROP TABLESPACE tpch_lineitem_data INCLUDING CONTENTS AND DATAFILES;
CREATE BIGFILE TABLESPACE tpch_lineitem_data
    DATAFILE '/u01/app/oradata/TPCH/tpch_lineitem_data01.dbf' 
    SIZE 10M REUSE
    AUTOEXTEND ON
    MAXSIZE UNLIMITED
    PERMANENT
    NOLOGGING
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
    SEGMENT SPACE MANAGEMENT AUTO
;

DROP TABLESPACE tpch_lineitem_idx INCLUDING CONTENTS AND DATAFILES;
CREATE BIGFILE TABLESPACE tpch_lineitem_idx
    DATAFILE '/u01/app/oradata/TPCH/tpch_lineitem_idx01.dbf' 
    SIZE 10M REUSE
    AUTOEXTEND ON
    MAXSIZE UNLIMITED
    PERMANENT
    NOLOGGING
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
    SEGMENT SPACE MANAGEMENT AUTO
;

/*
|| Listing 4.2:
|| Benchmark Factory’s XML-based configuration file that controls assignment of attributes
|| to tables and indexes for the TPC-H schema. NOTE: This version of the file has been edited
|| for sake of visibility!
*/

<?xml version="1.0" encoding="UTF-16"?>
<BMFBenchmarkMaps>
<ELEMENT CLASSNAME="COracleBenchmarkMappings"><OBJECTNAME>H_PART</OBJECTNAME><OBJECTTYPE>1</OBJECTTYPE><TABLESPACE>TPCH_PART_DATA</TABLESPACE>
  <PARALLEL>1</PARALLEL><DEGREE>Default</DEGREE><INSTANCES>Default</INSTANCES><LOGGING>0</LOGGING><CACHE>0</CACHE><MONITORING>0</MONITORING>
  <COMPUTESTATISTICS>0</COMPUTESTATISTICS><NOTSORTED>0</NOTSORTED></ELEMENT>
<ELEMENT CLASSNAME="COracleBenchmarkMappings"><OBJECTNAME>H_Supplier</OBJECTNAME><OBJECTTYPE>1</OBJECTTYPE><TABLESPACE>TPCH_SUPPLIER_DATA</TABLESPACE>
  <PARALLEL>1</PARALLEL><DEGREE>Default</DEGREE><INSTANCES>Default</INSTANCES><LOGGING>0</LOGGING><CACHE>0</CACHE><MONITORING>0</MONITORING>
  <COMPUTESTATISTICS>0</COMPUTESTATISTICS><NOTSORTED>0</NOTSORTED></ELEMENT>
<ELEMENT CLASSNAME="COracleBenchmarkMappings"><OBJECTNAME>H_Partsupp</OBJECTNAME><OBJECTTYPE>1</OBJECTTYPE><TABLESPACE>TPCH_PARTSUPP_DATA</TABLESPACE>
  <PARALLEL>1</PARALLEL><DEGREE>Default</DEGREE><INSTANCES>Default</INSTANCES><LOGGING>0</LOGGING><CACHE>0</CACHE><MONITORING>0</MONITORING>
  <COMPUTESTATISTICS>0</COMPUTESTATISTICS><NOTSORTED>0</NOTSORTED></ELEMENT>
<ELEMENT CLASSNAME="COracleBenchmarkMappings"><OBJECTNAME>H_Customer</OBJECTNAME><OBJECTTYPE>1</OBJECTTYPE><TABLESPACE>TPCH_CUSTOMER_DATA</TABLESPACE>
  <PARALLEL>1</PARALLEL><DEGREE>Default</DEGREE><INSTANCES>Default</INSTANCES><LOGGING>0</LOGGING><CACHE>0</CACHE><MONITORING>0</MONITORING>
  <COMPUTESTATISTICS>0</COMPUTESTATISTICS><NOTSORTED>0</NOTSORTED></ELEMENT>
<ELEMENT CLASSNAME="COracleBenchmarkMappings"><OBJECTNAME>H_Order</OBJECTNAME><OBJECTTYPE>1</OBJECTTYPE><TABLESPACE>TPCH_ORDER_DATA</TABLESPACE>
  <PARALLEL>1</PARALLEL><DEGREE>Default</DEGREE><INSTANCES>Default</INSTANCES><LOGGING>0</LOGGING><CACHE>0</CACHE><MONITORING>0</MONITORING>
  <COMPUTESTATISTICS>0</COMPUTESTATISTICS><NOTSORTED>0</NOTSORTED></ELEMENT>
<ELEMENT CLASSNAME="COracleBenchmarkMappings"><OBJECTNAME>H_Lineitem</OBJECTNAME><OBJECTTYPE>1</OBJECTTYPE><TABLESPACE>TPCH_LINEITEM_DATA</TABLESPACE>
  <PARALLEL>1</PARALLEL><DEGREE>Default</DEGREE><INSTANCES>Default</INSTANCES><LOGGING>0</LOGGING><CACHE>0</CACHE><MONITORING>0</MONITORING>
  <COMPUTESTATISTICS>0</COMPUTESTATISTICS><NOTSORTED>0</NOTSORTED></ELEMENT>
<ELEMENT CLASSNAME="COracleBenchmarkMappings"><OBJECTNAME>H_Nation</OBJECTNAME><OBJECTTYPE>1</OBJECTTYPE><TABLESPACE>TPCH_REF_DATA</TABLESPACE>
  <PARALLEL>1</PARALLEL><DEGREE>Default</DEGREE><INSTANCES>Default</INSTANCES><LOGGING>0</LOGGING><CACHE>0</CACHE><MONITORING>0</MONITORING>
  <COMPUTESTATISTICS>0</COMPUTESTATISTICS><NOTSORTED>0</NOTSORTED></ELEMENT>
<ELEMENT CLASSNAME="COracleBenchmarkMappings"><OBJECTNAME>H_Region</OBJECTNAME><OBJECTTYPE>1</OBJECTTYPE><TABLESPACE>TPCH_REF_DATA</TABLESPACE>
  <PARALLEL>1</PARALLEL><DEGREE>Default</DEGREE><INSTANCES>Default</INSTANCES><LOGGING>0</LOGGING><CACHE>0</CACHE><MONITORING>0</MONITORING>
  <COMPUTESTATISTICS>0</COMPUTESTATISTICS><NOTSORTED>0</NOTSORTED></ELEMENT>
<ELEMENT CLASSNAME="COracleBenchmarkMappings"><OBJECTNAME>H_lineitem_idx1</OBJECTNAME><OBJECTTYPE>2</OBJECTTYPE><TABLESPACE>TPCH_LINEITEM_IDX</TABLESPACE>
  <PARALLEL>1</PARALLEL><DEGREE>Default</DEGREE><INSTANCES>Default</INSTANCES><LOGGING>0</LOGGING><CACHE>0</CACHE><MONITORING>0</MONITORING>
  <COMPUTESTATISTICS>1</COMPUTESTATISTICS><NOTSORTED>0</NOTSORTED></ELEMENT>
<ELEMENT CLASSNAME="COracleBenchmarkMappings"><OBJECTNAME>H_orders_idx1</OBJECTNAME><OBJECTTYPE>2</OBJECTTYPE><TABLESPACE>TPCH_ORDERS_IDX</TABLESPACE>
  <PARALLEL>1</PARALLEL><DEGREE>Default</DEGREE><INSTANCES>Default</INSTANCES><LOGGING>0</LOGGING><CACHE>0</CACHE><MONITORING>0</MONITORING>
  <COMPUTESTATISTICS>1</COMPUTESTATISTICS><NOTSORTED>0</NOTSORTED></ELEMENT>
<ELEMENT CLASSNAME="COracleBenchmarkMappings"><OBJECTNAME>H_partsupp_idx1</OBJECTNAME><OBJECTTYPE>2</OBJECTTYPE><TABLESPACE>TPCH_PARTSUPP_IDX</TABLESPACE>
  <PARALLEL>1</PARALLEL><DEGREE>Default</DEGREE><INSTANCES>Default</INSTANCES><LOGGING>0</LOGGING><CACHE>0</CACHE><MONITORING>0</MONITORING>
  <COMPUTESTATISTICS>1</COMPUTESTATISTICS><NOTSORTED>0</NOTSORTED></ELEMENT>
<ELEMENT CLASSNAME="COracleBenchmarkMappings"><OBJECTNAME>H_customer_idx1</OBJECTNAME><OBJECTTYPE>2</OBJECTTYPE><TABLESPACE>TPCH_CUSTOMER_IDX</TABLESPACE>
  <PARALLEL>1</PARALLEL><DEGREE>Default</DEGREE><INSTANCES>Default</INSTANCES><LOGGING>0</LOGGING><CACHE>0</CACHE><MONITORING>0</MONITORING>
  <COMPUTESTATISTICS>1</COMPUTESTATISTICS><NOTSORTED>0</NOTSORTED></ELEMENT>
</BMFBenchmarkMaps>

/*
|| Listing 4.3:
|| Tables created during Benchmark Factory’s construction of the TPC-H schema 
*/


-----
-- Table: TPCH.H_CUSTOMER
-----
DROP TABLE tpch.h_customer PURGE;
CREATE TABLE tpch.h_customer (
     c_custkey    NUMBER NOT NULL
    ,c_name       VARCHAR2(25)
    ,c_address    VARCHAR2(40)
    ,c_nationkey  NUMBER
    ,c_phone      CHAR(15)
    ,c_acctbal    NUMBER
    ,c_mktsegment CHAR(10)
    ,c_comment    VARCHAR2(117)
)
TABLESPACE tpch_customer_data
  PCTFREE 10
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 1M
    NEXT 1M
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
    PCTINCREASE 0
  );

-----
-- Table: TPCH.H_LINEITEM
-----
DROP TABLE tpch.h_lineitem PURGE;
CREATE TABLE tpch.h_lineitem (
     l_orderkey      NUMBER NOT NULL 
    ,l_partkey       NUMBER NOT NULL 
    ,l_suppkey       NUMBER NOT NULL 
    ,l_linenumber    NUMBER NOT NULL 
    ,l_quantity      NUMBER NOT NULL 
    ,l_extendedprice NUMBER NOT NULL 
    ,l_discount      NUMBER NOT NULL 
    ,l_tax           NUMBER NOT NULL 
    ,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)
)
TABLESPACE tpch_lineitem_data
  PCTFREE 10
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 1M
    NEXT 1M
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
    PCTINCREASE 0
  );

-----
-- Table: TPCH.H_NATION
-----
DROP TABLE tpch.h_nation PURGE;
CREATE TABLE tpch.h_nation (
     n_nationkey NUMBER NOT NULL 
    ,n_name      CHAR(25) 
    ,n_regionkey NUMBER 
    ,n_comment   VARCHAR2(152)
)
TABLESPACE tpch_ref_data
  PCTFREE 10
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64k
    NEXT 1M
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
  );

-----
-- Table: TPCH.H_ORDER
-----
DROP TABLE tpch.h_order PURGE;
CREATE TABLE tpch.h_order (
     o_orderkey      NUMBER NOT NULL 
    ,o_custkey       NUMBER NOT NULL 
    ,o_orderstatus   CHAR(1) 
    ,o_totalprice    NUMBER 
    ,o_orderdate     DATE 
    ,o_orderpriority CHAR(15) 
    ,o_clerk         CHAR(15) 
    ,o_shippriority  NUMBER 
    ,o_comment       VARCHAR2(79)
)
TABLESPACE tpch_order_data
  PCTFREE 10
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 1M
    NEXT 1M
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
    PCTINCREASE 0
  );

-----
-- Table: TPCH.H_PART
-----
DROP TABLE tpch.h_part PURGE;
CREATE TABLE tpch.h_part (
     p_partkey     NUMBER NOT NULL 
    ,p_name        VARCHAR2(55) 
    ,p_mfgr        CHAR(25) 
    ,p_brand       CHAR(10) 
    ,p_type        VARCHAR2(25) 
    ,p_size        NUMBER 
    ,p_container   CHAR(10) 
    ,p_retailprice NUMBER 
    ,p_comment     VARCHAR2(23)
)
TABLESPACE tpch_part_data
  PCTFREE 10
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 1M
    NEXT 1M
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
    PCTINCREASE 0
  );

-----
-- Table: TPCH.H_PARTSUPP
-----
DROP TABLE tpch.h_partsupp PURGE;
CREATE TABLE tpch.h_partsupp (
     ps_partkey    NUMBER NOT NULL 
    ,ps_suppkey    NUMBER NOT NULL 
    ,ps_availqty   NUMBER 
    ,ps_supplycost NUMBER NOT NULL 
    ,ps_comment    VARCHAR2(199)
)
TABLESPACE tpch_partsupp_data
  PCTFREE 10
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 1M
    NEXT 1M
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
    PCTINCREASE 0
  );

-----
-- Table: TPCH.H_REGION
-----
DROP TABLE tpch.h_region PURGE;
CREATE TABLE tpch.h_region (
     r_regionkey NUMBER 
    ,r_name      CHAR(25) 
    ,r_comment   VARCHAR2(152)
)
TABLESPACE tpch_ref_data
  PCTFREE 10
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    NEXT 1M
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
  );

-----
-- Table: TPCH.H_SUPPLIER
-----
DROP TABLE tpch.h_supplier PURGE;
CREATE TABLE tpch.h_supplier (
     s_suppkey   NUMBER NOT NULL 
    ,s_name      CHAR(25) 
    ,s_address   VARCHAR2(40) 
    ,s_nationkey NUMBER 
    ,s_phone     CHAR(15) 
    ,s_acctbal   NUMBER 
    ,s_comment   VARCHAR2(101)
)
TABLESPACE tpch_supplier_data
  PCTFREE 10
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 1M
    NEXT 1M
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
    PCTINCREASE 0
  );

/* 
|| Listing 4.4:
|| Indexes created during Benchmark Factory’s construction of the TPC-H schema
*/

/*
|| Script:  Create_TPCH_Indexes.sql
|| Purpose: Drops and recreates all indexes in the TPC-H schema
|| Author:  Jim Czuprynski
*/

-----
-- Table: TPCH.H_CUSTOMER
-- Index: TPCH.H_CUSTOMER_IDX1
-----
DROP INDEX tpch.h_customer_idx1;
CREATE UNIQUE INDEX tpch.h_customer_idx1 
  ON tpch.h_customer (c_custkey)
  TABLESPACE tpch_customer_idx
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 1M
    NEXT 1M
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
    PCTINCREASE 0
  );

-----
-- Table: TPCH.H_LINEITEM
-- Index: TPCH.H_LINEITEM_IDX1
-----
DROP INDEX tpch.h_lineitem_idx1;
CREATE INDEX tpch.h_lineitem_idx1 
  ON tpch.h_lineitem (l_orderkey)
  TABLESPACE tpch_lineitem_idx
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 1M
    NEXT 1M
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
    PCTINCREASE 0
  );

-----
-- Table: TPCH.H_ORDER
-- Index: TPCH.H_ORDERS_IDX1
-----
DROP INDEX tpch.h_orders_idx1;
CREATE UNIQUE INDEX tpch.h_orders_idx1 
  ON tpch.h_order (o_orderkey)
  TABLESPACE tpch_order_idx
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 1M
    NEXT 1M
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
    PCTINCREASE 0
  );

-----
-- Table: TPCH.H_H_PARTSUPP
-- Index: TPCH.H_PARTSUPP_IDX1
-----
DROP INDEX tpch.h_partsupp_idx1;
CREATE UNIQUE INDEX tpch.h_partsupp_idx1 
  ON tpch.h_partsupp (ps_partkey, ps_suppkey)
  TABLESPACE tpch_partsupp_idx
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 1M
    NEXT 1M
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
    PCTINCREASE 0
  );