|| 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
);