oracle tpch建表语句,IQ性能调优示例——执行TPCH查询IQ与Oracle的比较(1)

在先前的博文讲述了在装载完TPCH 1GB数据之后启动IQ Server，执行TPCH定义的一些典型查询的执行情况。在下面说明一下在相同环境、表和数据情况下，Oracle 10g R2执行的情况。这里只是客观的说明IQ的特点和优势，并没有诋毁Oracle的意思。毕竟世上没有十全十美的产品。

对于Oracle 11g R2的执行情况，有兴趣读者可以自行测试，如果有结果记得给我反馈呀(tigeriq123@163.com)

1. Oracle建库脚本

#initTPCHDB.2011.ora

audit_file_dest='/opt/oracle/admin/TPCHDB/adump'

background_dump_dest='/opt/oracle/admin/TPCHDB/bdump'

core_dump_dest='/opt/oracle/admin/TPCHDB/cdump'

user_dump_dest='/opt/oracle/admin/TPCHDB/udump'

compatible='10.2.0.1'

db_block_checksum=FALSE

db_block_size=8192

db_create_file_dest='+DATA_DISKGP1'

db_domain=''

db_file_multiblock_read_count=16

db_name='TPCHDB'

db_writer_processes=2

global_names=FALSE

log_checkpoints_to_alert=TRUE

nls_date_format=YYYY-MM-DD

optimizer_mode=CHOOSE

db_recovery_file_dest='+RECO_DISKGP1'

db_recovery_file_dest_size=1887436800

job_queue_processes=10

log_archive_dest_1='LOCATION=+RECO_DISKGP1/TPCHDB/'

log_archive_format='%t_%s_%r.dbf'

open_cursors=600

pga_aggregate_target=167772160

processes=300

remote_login_passwordfile='EXCLUSIVE'

sga_max_size=314572800

sga_target=314572800

undo_management='AUTO'

undo_tablespace='TS_UNDO'

#postsetdb.sql

set feedback off

set pages 0

set line 3000

set trims on

set ver off

set heading off

set timing off

set echo off

set show off

set term off

spool /tmp/set_ctlfile

select 'alter system set control_files=''' || REPLACE(value,', ', ''',''') || ''' scope=spfile;' from v$parameter where name = 'cont

rol_files';

spool off

#createdb.sh 使用ASM作为存储管理

echo Start Database Creation at `date`

mkdir -p $ORACLE_BASE/admin/TPCHDB/adump

mkdir -p $ORACLE_BASE/admin/TPCHDB/bdump

mkdir -p $ORACLE_BASE/admin/TPCHDB/cdump

mkdir -p $ORACLE_BASE/admin/TPCHDB/dpdump

mkdir -p $ORACLE_BASE/admin/TPCHDB/pfile

mkdir -p $ORACLE_BASE/admin/TPCHDB/udump

mkdir -p $ORACLE_HOME/cfgtoollogs/dbca/TPCHDB

rm -f $ORACLE_HOME/dbs/initTPCHDB.ora

cp ./initTPCHDB.2011.ora $ORACLE_HOME/dbs/initTPCHDB.ora

sqlplus / as sysdba<

set echo on

set timing on

shutdown abort;

startup pfile=?/dbs/initTPCHDB.ora nomount;

create database

user sys identified by "oracle"

user system identified by "oracle"

maxdatafiles 100

maxinstances 1

character set ZHS16GBK

national character set AL16UTF16

set default smallfile tablespace

logfile '+DATA_DISKGP1' size 10m ,

'+RECO_DISKGP1' size 10m

maxlogfiles 16

maxlogmembers 3

maxloghistory 1

noarchivelog

extent management local

datafile '+DATA_DISKGP1'

size 300m

sysaux datafile '+DATA_DISKGP1'

size 200m

default temporary tablespace ts_temp

tempfile '+DATA_DISKGP1'

size 1500m

extent management local uniform size 1m

smallfile undo tablespace ts_undo

datafile '+DATA_DISKGP1'

size 300m

;

set termout off

set echo off

spool /tmp/cat

@?/rdbms/admin/catalog.sql;

@?/rdbms/admin/catparr.sql;

@?/rdbms/admin/catproc.sql;

@?/rdbms/admin/utlxplan.sql;

connect system/oracle

@?/sqlplus/admin/pupbld.sql;

connect sys/oracle as sysdba

CREATE TABLESPACE TPCH01

DATAFILE '+DATA_DISKGP1/TPCHDB/datafile/TPCH01_DATA.dbf' SIZE 2048M AUTOEXTEND OFF

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M

LOGGING

ONLINE

SEGMENT SPACE MANAGEMENT AUTO;

ALTER DATABASE DEFAULT TABLESPACE TPCH01;

CREATE SPFILE='+DATA_DISKGP1/TPCHDB/spfileTPCHDB.ora' FROM PFILE;

@postsetdb.sql

shutdown immediate;

exit;

EOF

echo End Database Creation at `date`

#createdb_post.sh

echo "spfile='+DATA_DISKGP1/TPCHDB/spfileTPCHDB.ora'" > $ORACLE_HOME/dbs/initTPCHDB.ora

sqlplus / as sysdba<

shutdown abort;

startup;

@/tmp/set_ctlfile.lst

shutdown immediate;

exit;

EOF

说明：

(1) 先执行createdb.sh，然后执行creaetdb_post.sh.

(2) 在执行createdb.sh之前，需要准备好ASM集DISK GROUPS,初始化参数文件和postsetdb.sql脚本

2. 创建表

(1) 没有使用compress和nologging选项

create table part

(p_partkey int not null,

p_name varchar(55),

p_mfgr char(25),

p_brand char(10),

p_type varchar(25),

p_size int,

p_container char(10),

p_retailprice numeric(10,2),

p_comment varchar(23),

primary key(p_partkey)

);

create table region

(r_regionkey int not null,

r_name char(25),

r_comment varchar(152),

primary key(r_regionkey)

);

create table nation

(n_nationkey int not null,

n_name char(25),

n_regionkey int,

n_comment varchar(152),

primary key(n_nationkey),

foreign key (n_regionkey) references region(r_regionkey)

);

create table supplier

(s_suppkey int not null,

s_name char(25),

s_address varchar(40),

s_nationkey int,       --Foreign key to N_NATIONKEY

s_phone char(15),

s_acctbal numeric(15,2),

s_comment varchar(101),

primary key(s_suppkey),

foreign key (s_nationkey) references nation(n_nationkey)

);

create table partsupp

(ps_partkey int not null,  --Foreign key to P_PARTKEY

ps_suppkey int not null,  --Foreign key to S_SUPPKEY

ps_availqty int,

ps_supplycost numeric(10,2),

ps_comment varchar(199),

primary key(ps_partkey,ps_suppkey),

foreign key (ps_partkey) references part(p_partkey),

foreign key (ps_suppkey) references supplier(s_suppkey)

);

create table customer

(c_custkey int not null,

c_name varchar(25),

c_address varchar(40),

c_nationkey int,          --Foreign key to N_NATIONKEY

c_phone char(15),

c_acctbal numeric(15,2),

c_mktsegment char(10),

c_comment varchar(117),

primary key(c_custkey),

foreign key (c_nationkey) references nation(n_nationkey)

);

create table orders

(o_orderkey int not null,

o_custkey int,           --Foreign key to C_CUSTKEY

o_orderstatus char(1),

o_totalprice numeric(15,2),

o_orderdate date,

o_orderpriority char(15),

o_clerk char(15),

o_shippriority int,

o_comment varchar(79),

primary key(o_orderkey),

foreign key (o_custkey) references customer(c_custkey)

);

create table lineitem

(l_orderkey int not null,

l_partkey int,

l_suppkey int,

l_linenumber int not null,

l_quantity numeric(10,2),

l_extendedprice numeric(10,2),

l_discount numeric(10,2),

l_tax numeric(10,2),

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 varchar(44),

primary key(l_orderkey,l_linenumber),

foreign key (l_orderkey) references orders(o_orderkey),

foreign key (l_partkey,l_suppkey) references partsupp(ps_partkey,ps_suppkey)

);

(2) 使用了compress和nologging选项

drop table lineitem;

drop table orders;

drop table partsupp;

drop table supplier;

drop table part;

drop table customer;

drop table nation;

drop table region;

purge table lineitem;

purge table orders;

purge table partsupp;

purge table supplier;

purge table part;

purge table customer;

purge table nation;

purge table region;

create table part

(p_partkey int not null,

p_name varchar(55),

p_mfgr char(25),

p_brand char(10),

p_type varchar(25),

p_size int,

p_container char(10),

p_retailprice numeric(10,2),

p_comment varchar(23),

primary key(p_partkey)

)

compress

nologging;

create table region

(r_regionkey int not null,

r_name char(25),

r_comment varchar(152),

primary key(r_regionkey)

)

;

create table nation

(n_nationkey int not null,

n_name char(25),

n_regionkey int,

n_comment varchar(152),

primary key(n_nationkey),

foreign key (n_regionkey) references region(r_regionkey)

)

;

create table supplier

(s_suppkey int not null,

s_name char(25),

s_address varchar(40),

s_nationkey int,       --Foreign key to N_NATIONKEY

s_phone char(15),

s_acctbal numeric(15,2),

s_comment varchar(101),

primary key(s_suppkey),

foreign key (s_nationkey) references nation(n_nationkey)

)

compress

nologging

;

create table partsupp

(ps_partkey int not null,  --Foreign key to P_PARTKEY

ps_suppkey int not null,  --Foreign key to S_SUPPKEY

ps_availqty int,

ps_supplycost numeric(10,2),

ps_comment varchar(199),

primary key(ps_partkey,ps_suppkey),

foreign key (ps_partkey) references part(p_partkey),

foreign key (ps_suppkey) references supplier(s_suppkey)

)

compress

nologging

;

create table customer

(c_custkey int not null,

c_name varchar(25),

c_address varchar(40),

c_nationkey int,          --Foreign key to N_NATIONKEY

c_phone char(15),

c_acctbal numeric(15,2),

c_mktsegment char(10),

c_comment varchar(117),

primary key(c_custkey),

foreign key (c_nationkey) references nation(n_nationkey)

)

compress

nologging

;

create table orders

(o_orderkey int not null,

o_custkey int,           --Foreign key to C_CUSTKEY

o_orderstatus char(1),

o_totalprice numeric(15,2),

o_orderdate date,

o_orderpriority char(15),

o_clerk char(15),

o_shippriority int,

o_comment varchar(79),

primary key(o_orderkey),

foreign key (o_custkey) references customer(c_custkey)

)

compress

nologging

;

create table lineitem

(l_orderkey int not null,

l_partkey int,

l_suppkey int,

l_linenumber int not null,

l_quantity numeric(10,2),

l_extendedprice numeric(10,2),

l_discount numeric(10,2),

l_tax numeric(10,2),

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 varchar(44),

primary key(l_orderkey,l_linenumber),

foreign key (l_orderkey) references orders(o_orderkey),

foreign key (l_partkey,l_suppkey) references partsupp(ps_partkey,ps_suppkey)

)

compress

nologging

;