Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1560096
  • 博文数量: 201
  • 博客积分: 2812
  • 博客等级: 少校
  • 技术积分: 3029
  • 用 户 组: 普通用户
  • 注册时间: 2011-01-18 18:28
个人简介

从事数据库工作多年,目前看好分布式NeSQL/HTAP数据库在企业客户市场的发展。未来的主要方向是——致力于 NewSQL/HTAP 数据库的推广普及。

文章存档

2016年(1)

2015年(8)

2014年(23)

2013年(50)

2012年(32)

2011年(87)

分类: Sybase

2011-02-23 13:22:34

    在先前的博文讲述了在装载完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
阅读(3267) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~