系统环境:
Window XP ,
Oracle 10.2
内存 2g
本机测试,两张表的数据量大于9百万条数据记录.
测试任务:
测试使用分区表与不使用分区表对海量数据操作的影响
▲每个统计表中的第三条测试语句为直接查询分区表后的统计数据。
第一部分测试
理想状态下的测试,以ID为主键,以ID的大小作为分区表的大小。
测试环境:
表1:TestTable01
一个主键ID,两个date类型,64个double类型的字段,使用三个分区表,按ID号的索引大小来分区存储。
表2:TestTable02
一个主键ID,两个date类型,64个double类型的字段,存储在一个表中。
1. 统计当前表中记录数
测试语句 | 查询时间 | 读内存次数 | 硬盘读取次数 | 共享内存占用 |
select count(t_id) from testtable02 | 04.25s | 36428 | 18068 | 8648 |
select count(t_id) from testtable01 | 02.53s | 46503 | 21236 | 4552 |
2. 查询当前表中id索引的最大值
测试语句 | 查询时间 | 读内存次数 | 硬盘读取次数 | 共享内存占用 |
select max(t_id) from testtable02 | 0.59s | 223 | 455 | 8646 |
select max(t_id) from testtable01 | 0.62s | 452 | 221 | 8646 |
select max(t_id) from testtable01 partition (part_03) | 3:13.53 (min) | 204845 | 199458 | 12762 |
3.取ID在999 99~99900的区间值 数据量 :98条记录
测试语句 | 查询时间 | 读内存次数 | 硬盘读取次数 | 共享内存占用 |
select * from testtable02 where t_id>99900 and t_id<99999; | 0.95s | 379 | 458 | 33246 |
select * from testtable01 where t_id>99900 and t_id<99999; | 0.87s | 565 | 230 | 33246 |
select * from testtable01 partition (part_01) where t_id>99900 and t_id<99999; | 0.34s | 118 | 0 | 33266 |
第二部分测试
以时间段作为分区表的条件,进行分区。
测试环境:
表1:TestTable01
一个主键ID,两个date类型,64个double类型的字段,使用三个分区表,按两个时间段来分区存储。
表2:TestTable02
一个主键ID,两个date类型,64个double类型的字段,存储在一个表中。
1. 查询表中时间段的最大值:
▲资源(I/O次数,CPU等)消耗评估指数越高表示使用的资源多,执行计划的代价大,oracle会以执行计划的代价大小作为衡量标准,优化器选择代价最小的执行计划作为真正执行该查询的执行计划,并抛弃其它的执行计划。
测试语句 | 查询时间 | 读内存次数 | 硬盘读取次数 | 资源(I/O次数,CPU等)消耗评估指数 | 共享内存占用 |
select max(startdate) from testtable02 ; | 51.62s | 450366 | 418589 | 99418 | 8652 |
select max(startdate) from testtable01 ; | 55.31s | 529739 | 529482 | 25952 | 4556 |
select max(startdate) from testtable01 partition(part_02); | 42.43s | 411806 | 411778 | 2 | 8672 |
2查询某一时间段的数据,都返回1条记录
已知:
表1的最大时间段值:2010-06-12 10:33:27
表2的最大时间段值:2010-06-12 10:48:03
测试语句 | 查询时间 | 读内存次数 | 硬盘读取次数 | 资源(I/O次数,CPU等)消耗评估指数 | 共享内存占用 |
select * from testtable02 where startdate>to_date('2010-06-12 10:48:02','yyyy-mm-dd hh24:mi:ss') and startdate<sysdate ; | 51.53s | 453072 | 418251 | 99531 | 29213 |
select * from testtable01 where startdate>to_date('2010-06-12 10:33:26','yyyy-mm-dd hh24:mi:ss') and startdate<sysdate; | 42.32s | 412013 | 411760 | 2 | 33316 |
select * from testtable01 partition (part_02) where startdate>to_date('2010-06-12 10:33:26','yyyy-mm-dd hh24:mi:ss') and startdate<sysdate; | 42.78s | 412595 | 411802 | 2 | 33327 |
第三部分测试
给两张表的字段startdate分别建立索引,其它环境同第二部分一样。
1.查询表中时间段的最大值:
测试语句 | 查询时间 | 读内存次数 | 硬盘读取次数 | 资源(I/O次数,CPU等)消耗评估指数 | 共享内存占用 |
select max(startdate) from testtable02 ; | 0.53s | 120 | 372 | 99418 | 12748 |
select max(startdate) from testtable01 ; | 0.09s | 140 | 8 | 25952 | 12748 |
select max(startdate) from testtable01 partition(part_02); | 0.01s | 3 | 0 | 2 | 12767 |
2查询某一时间段的数据,都返回500条记录
已知:
表1的最大时间段值:2010-06-12 14:56:11
表2的最大时间段值:2010-06-12 14:59:08
测试语句 | 查询时间 | 读内存次数 | 硬盘读取次数 | 资源(I/O次数,CPU等)消耗评估指数 | 共享内存占用 |
select * from testtable02 where startdate>to_date('2010-06-12 14:59:01','yyyy-mm-dd hh24:mi:ss') and startdate<sysdate ; | 2.26s | 442 | 413 | 28 | 33309 |
select * from testtable01 where startdate>to_date('2010-06-12 14:55:38','yyyy-mm-dd hh24:mi:ss') and startdate<sysdate; | 1.82s | 982 | 122 | 1 | 33308 |
select * from testtable01 partition (part_03) where startdate>to_date('2010-06-12 14:55:38','yyyy-mm-dd hh24:mi:ss') and startdate<sysdate; | 1.7s | 306 | 91 | 2 | 33327 |
测试结果总结
经测试结果数据表明,使用分区表+字段索引的方式可以大大的提高查询性能,能降低磁盘I/O次数,增强可用性和维护性,在表的某个分区出现故障,表在其他分区的数据仍然可用;能减低cpu的消耗,减少内存的使用。
以上测试数据结果都是根据oracle内部性能工具获得,具体数据跟系统环境的有关。
附分区表建表代码:
======创建表空间 ======
create tablespace testspace01
datafile 'L:/oracle/product/10.2.0/oradata/orcl/testspace01.dbf' size 4000m;
create tablespace testspace02
datafile 'L:/oracle/product/10.2.0/oradata/orcl/testspace02.dbf' size 6500m;
create tablespace testspace03
datafile 'L:/oracle/product/10.2.0/oradata/orcl/testspace03.dbf' size 10000M;
======创建table并分区======
CREATE TABLE testtable01 (
t_id number NOT NULL ,
startdate date default sysdate,
enddate date default sysdate-1,
a1 number(22,4) default 2000.011,
a2 number(22,4) default 2000.011,
a3 number(22,4) default 2000.011,
a4 number(22,4) default 2000.011,
a5 number(22,4) default 2000.011,
a6 number(22,4) default 2000.011,
a7 number(22,4) default 2000.011,
a8 number(22,4) default 2000.011,
a9 number(22,4) default 2000.011,
a10 number(22,4) default 2000.011,
a11 number(22,4) default 2000.011,
a12 number(22,4) default 2000.011,
a13 number(22,4) default 2000.011,
a14 number(22,4) default 2000.011,
a15 number(22,4) default 2000.011,
a16 number(22,4) default 2000.011,
a17 number(22,4) default 2000.011,
a18 number(22,4) default 2000.011,
a19 number(22,4) default 2000.011,
a20 number(22,4) default 2000.011,
a21 number(22,4) default 2000.011,
a22 number(22,4) default 2000.011,
a23 number(22,4) default 2000.011,
a24 number(22,4) default 2000.011,
a25 number(22,4) default 2000.011,
a26 number(22,4) default 2000.011,
a27 number(22,4) default 2000.011,
a28 number(22,4) default 2000.011,
a29 number(22,4) default 2000.011,
a30 number(22,4) default 2000.011,
a31 number(22,4) default 2000.011,
a32 number(22,4) default 2000.011,
a33 number(22,4) default 2000.011,
a34 number(22,4) default 2000.011,
a35 number(22,4) default 2000.011,
a36 number(22,4) default 2000.011,
a37 number(22,4) default 2000.011,
a38 number(22,4) default 2000.011,
a39 number(22,4) default 2000.011,
a40 number(22,4) default 2000.011,
a41 number(22,4) default 2000.011,
a42 number(22,4) default 2000.011,
a43 number(22,4) default 2000.011,
a44 number(22,4) default 2000.011,
a45 number(22,4) default 2000.011,
a46 number(22,4) default 2000.011,
a47 number(22,4) default 2000.011,
a48 number(22,4) default 2000.011,
a49 number(22,4) default 2000.011,
a50 number(22,4) default 2000.011,
a51 number(22,4) default 2000.011,
a52 number(22,4) default 2000.011,
a53 number(22,4) default 2000.011,
a54 number(22,4) default 2000.011,
a55 number(22,4) default 2000.011,
a56 number(22,4) default 2000.011,
a57 number(22,4) default 2000.011,
a58 number(22,4) default 2000.011,
a59 number(22,4) default 2000.011,
a60 number(22,4) default 2000.011,
a61 number(22,4) default 2000.011,
a62 number(22,4) default 2000.011,
a63 number(22,4) default 2000.011,
a64 number(22,4) default 2000.011,
primary key(t_id )
)
partition by range (startdate)
(partition part_01 values less than (to_date('2010-06-12 08:08:08','yyyy-mm-dd hh24:mi:ss')) tablespace testspace01,
partition part_02 values less than (to_date('2010-06-12 10:38:08','yyyy-mm-dd hh24:mi:ss')) tablespace testspace02,
partition part_03 values less than (maxvalue) tablespace testspace03);
======给startdate建立索引 ======
create index t01_sdate_index on testtable01(startdate)
local
(
partition idx_1 tablespace testspace01,
partition idx_2 tablespace testspace02,
partition idx_3 tablespace testspace03
);