3. 装载数据
(1) sqlldr控制文件
#customer.ctl
load data
infile '/home/oracle/tpchdb/data/customer.tbl' "str '|\n'"
into table customer
fields terminated by '|' optionally enclosed by '"'
(
c_custkey ,
c_name ,
c_address ,
c_nationkey ,
c_phone ,
c_acctbal ,
c_mktsegment ,
c_comment
)
#lineitem.ctl
load data
infile '/home/oracle/tpchdb/data/lineitem.tbl.1' "str '|\n'"
infile '/home/oracle/tpchdb/data/lineitem.tbl.2' "str '|\n'"
infile '/home/oracle/tpchdb/data/lineitem.tbl.3' "str '|\n'"
infile '/home/oracle/tpchdb/data/lineitem.tbl.4' "str '|\n'"
append
into table lineitem
fields terminated by '|' optionally enclosed by '"'
(
l_orderkey ,
l_partkey ,
l_suppkey ,
l_linenumber ,
l_quantity ,
l_extendedprice,
l_discount ,
l_tax ,
l_returnflag ,
l_linestatus ,
l_shipdate ,
l_commitdate ,
l_receiptdate ,
l_shipinstruct ,
l_shipmode ,
l_comment
)
#nation.ctl
load data
infile '/home/oracle/tpchdb/data/nation.tbl' "str '|\n'"
into table nation
fields terminated by '|' optionally enclosed by '"'
(
n_nationkey ,
n_name ,
n_regionkey ,
n_comment
)
#orders.ctl
load data
infile '/home/oracle/tpchdb/data/orders.tbl' "str '|\n'"
into table orders
fields terminated by '|' optionally enclosed by '"'
(
o_orderkey ,
o_custkey ,
o_orderstatus ,
o_totalprice ,
o_orderdate ,
o_orderpriority,
o_clerk ,
o_shippriority,
o_comment
)
#part.ctl
load data
infile '/home/oracle/tpchdb/data/part.tbl' "str '|\n'"
into table part
fields terminated by '|' optionally enclosed by '"'
(
p_partkey ,
p_name ,
p_mfgr ,
p_brand ,
p_type ,
p_size ,
p_container ,
p_retailprice ,
p_comment
)
#partsupp.ctl
load data
infile '/home/oracle/tpchdb/data/partsupp.tbl' "str '|\n'"
into table partsupp
fields terminated by '|' optionally enclosed by '"'
(
ps_partkey ,
ps_suppkey ,
ps_availqty ,
ps_supplycost ,
ps_comment
)
#region.ctl
load data
infile '/home/oracle/tpchdb/data/region.tbl' "str '|\n'"
into table region
fields terminated by '|' optionally enclosed by '"'
(
r_regionkey ,
r_name ,
r_comment
)
#supplier.ctl
load data
infile '/home/oracle/tpchdb/data/supplier.tbl' "str '|\n'"
into table supplier
fields terminated by '|' optionally enclosed by '"'
(
s_suppkey ,
s_name ,
s_address ,
s_nationkey ,
s_phone ,
s_acctbal ,
s_comment
)
(2) 装载数据
(3) 执行装载
#direct_load_tables.sh
date '+%X'
sqlldr userid=tpch_user/tpchpwd control=part.ctl direct=true parallel=false readsize=4096000 bindsize=4096000 rows=1000
sqlldr userid=tpch_user/tpchpwd control=region.ctl direct=true parallel=false readsize=4096000 bindsize=4096000 rows=1000
sqlldr userid=tpch_user/tpchpwd control=nation.ctl direct=true parallel=false readsize=4096000 bindsize=4096000 rows=1000
sqlldr userid=tpch_user/tpchpwd control=supplier.ctl direct=true parallel=false readsize=4096000 bindsize=4096000 rows=1000
sqlldr userid=tpch_user/tpchpwd control=partsupp.ctl direct=true parallel=false readsize=4096000 bindsize=4096000 rows=1000
sqlldr userid=tpch_user/tpchpwd control=customer.ctl direct=true parallel=false readsize=4096000 bindsize=4096000 rows=1000
sqlldr userid=tpch_user/tpchpwd control=orders.ctl direct=true parallel=false readsize=4096000 bindsize=4096000 rows=10000
sqlldr userid=tpch_user/tpchpwd control=lineitem.ctl direct=true parallel=false readsize=4096000 bindsize=4096000 rows=10000
date '+%X'
4. 数据装载执行情况说明
(1) 数据压缩情况
原始数据尺寸:1050M
IQ数据库中尺寸(TPCH_USER_MAIN dbspace):672M
IQ存放用户数据尺寸只有原始数据尺寸的64%
在同样的环境、表定义和数据下,测试了Oracle 10g R2。测试结果如下:
原始数据尺寸:1050M
Oracle数据库中尺寸(TPCH01 tablespace):
1420M (建表时没有使用压缩)
1011M (建表时使用了compress选项)
Oracle存放用户数据尺寸是原始数据尺寸的1.35倍 是IQ的2.11倍;即使采用了压缩也比IQ占用的空间大.
(2) 装载时间情况
下面是在我个人的笔记本(CPU:Intel Core i5 M540 2.53GHz,MEMORY:4GB)上使用vmware虚拟出的Linux虚机(虚机分配的内存为1G)上执行装载时的执行时间数据(以记录最多的表lineitem为例)
db_file_multiblock_read_count compress 装载时间(lineitem表) ======================================================================
16 N 00:01:03.57
16 Y 00:01:46.99
对于记录数最大、表最宽的lineitem,IQ装载时间只需
37.96秒、装载速度大约17万多行/秒!由此可以看到IQ的简单、较高的压缩能力、较高的性能等特点!
在同样的环境、表定义和数据下,Oracle 10g R2的测试结果如下(以最大的lineitme表为例)
(1) 使用sqlldr工具,dirtect=true,在不压缩的情况下执行时间:00:01:03.57
(2) 使用sqlldr工具,dirtect=true,在压缩的情况下执行时间:00:01:46.99
阅读(3343) | 评论(0) | 转发(0) |