在先前的博文讲述了在装载完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
;
