oracle计算表数据内存,oracle采用分区表+字段索引测试存储大量数据

系统环境:

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 testtable01partition(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 testtable02where startdate>to_date('2010-06-12 10:48:02','yyyy-mm-dd hh24:mi:ss') and startdate

51.53s

453072

418251

99531

29213

select * from testtable01where startdate>to_date('2010-06-12 10:33:26','yyyy-mm-dd hh24:mi:ss') and startdate

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

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 testtable01partition(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 testtable02where startdate>to_date('2010-06-12 14:59:01','yyyy-mm-dd hh24:mi:ss') and startdate

2.26s

442

413

28

33309

select * from testtable01where startdate>to_date('2010-06-12 14:55:38','yyyy-mm-dd hh24:mi:ss') and startdate

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

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

);