验证Oracle处理速度

（这是2009年写的东西了，在网上看到有人对数据库批量操作的‘速度’比较关注，于是就把这篇老文章整理了一下）

 

一、环境及前提

在244上（一台稍好一些的机器，做了RAID，机械硬盘，Raid几忘了），对eprk_person_flow表进行复制、更新，验证在Oracle数据库中操纵数据的各种方法的速度。共35,629,784条记录。

 

二、复制：直接建表

create /*+parallel*/table tt1 as select /*+parallel(pf, 4)*/ * from eprk_person_flow pf;

结果：
第一次：126.297秒，约每秒写入28.2W条数据。
第二次：149.109秒，约每秒写入23.9W条数据。

drop table tt1 purge;

 

三、复制：使用BULK COLLECT批量处理

create table tt1 as select * from eprk_person_flow where 1 = 2;

--   以下代码还有一个可以参考的地方：没有根据表结构声明太多的类型及变量。
declare
cursor cur_temp is
select /*+parallel(pf, 4)*/* from eprk_person_flow pf;

type t_person_flow is tableof cur_temp%rowtype;   -- 注意：这句很关键，直接引用上面的游标来定义类型

v_person_flow t_person_flow; begin
open cur_temp;

loop
fetch cur_temp  bulk collect into v_person_flow
limit 1000;

forall i in 1..v_person_flow.last
insert /*+append nologging*/ into tt1 values v_person_flow(i);-- 注意：这里values后没有扩号

exit when cur_temp%notfound; end loop;

closecur_temp; end;

drop tablett1;

结果：

第一次：768.828秒。约每秒写入4.6W条数。

第二次：505.953秒。约每秒写入7.0W条数据。(加hint，把limit从200改为1000)如果在目标表上建立索引后再插入，哪怕只建立一个主键约束，耗时也将*2还多。

在复制数据时，DDL对比DML语句，在速度上还有很有优势的。

 

四、更新

直接更新10%、20%、50%、100%的数据。

/*
建表并建立索引
*/
create /*+parallel*/table tt1 as select /*+parallel(pf, 4)*/ * from eprk_person_flow pf; -- 147s

alter table tt1
add constraint PK_tt1_PERSON_FLOW  primary key (SERIAL_NUMBER) using index
tablespace NNC_INDEX01 pctfree 10
initrans 2
maxtrans 255 storage
(
initial 64M minextents 1
maxextents  unlimited
);-- 102s

create index I_TT1_PERSON_FLOW_TT   on TT1 (PK_PERSON_ACCOUNT,   PK_TRADETYPE)
tablespace NNC_INDEX01 pctfree 10
 
initrans 2
maxtrans 255 storage
(
initial 128M
minextents 1
maxextents unlimited
);-- 200s

create index I_TT1_PERSON_FLOW_VN   on TT1 (VOUCHER_NUMBER) tablespace NNC_INDEX01
pctfree 10 initrans 2
maxtrans 255
storage (
initial 128M minextents 1
maxextents unlimited
);-- 88s

-- 更新数据(10%)
declare
i integer;

-- 从tt1表中，取10%的数据
cursor cur_temp is
select serial_number from tt1
where rownum < cast((35629784 / 10) as integer);
type t_serial_number is tableof tt1.serial_number%type index by pls_integer; v_serial_number t_serial_number;
begin
open cur_temp;

loop
fetch cur_temp  bulk collect into v_serial_number limit 1000;

forall i in 1..v_serial_number.count update /*+nologging*/ tt1
set occur_cash = 1, occur_unit = 1, cash_balance=1 , balance= 1 where serial_number = v_serial_number(i);

exit when cur_temp%notfound;
end loop;

closecur_temp; end;

更新10%的数据，共更新356W数据，耗时86.515秒，平均约每秒更新4.1W条。
更新20%的数据，共更新712W数据，耗时161.485秒，平均约每秒更新4 .4W条。
更新50%的数据，共更新1781W数据，耗时427.766秒，平均约每秒更新4.1W条。
更新100%的数据，共更新3562W数据，耗时1118.063秒，平均约每秒更新3.2W条。

在更新过程中，游标只读取1个字段，更新4个字段，由于该表有3个索引，虽然更新的字段都不在索引上，发现写入的数据量是读取的数据量 的20倍以上。（数据库归档日志打开）
以上只测试单表，并且顺序读取数据，然后根据唯一索引进行更新，如果有多表关联等要求，情况会更复杂。所以实际过程中，速度应该达不到4W/秒。

drop table tt1;

 

五、更新：使用中间表进行更新，并通过判断更新的数据量，进行对应的DDL操作。

-- 更新10%的数据。
-- 建立临时表
create global temporary table temp_tt1  as select * from tt1 where 1 = 2; -- 0.2s
-- 插入数据
insert into temp_tt1
select * from tt1
where rownum < cast((35629784 / 10) as integer);-- 12s
-- 更新临时表中的数据
update /*+parallel(temp_tt1, 4) nologging */ temp_tt1
set occur_cash = 1, occur_unit = 1, cash_balance=1 , balance= 1;-- 60s
 
分支一：
-- 删除源表中的数据
delete from tt1 t1 where exists (select 1 from temp_tt1  temp1 where t1.serial_number = temp1.serial_number);-- 163s
-- 把临时表中的数据插入到源表中
insert /*+parallel(tt1, 4)*/ into tt1 select /*+parallel(temp_tt1, 4)*/* from temp_tt1;--240s
分支二：
-- 使用临时表数据更新目标表
update tt1 t1 set (occur_cash, occur_unit, cash_balance, balance)
= (
select occur_cash, occur_unit, cash_balance, balance from temp_tt1  temp
where t1.serial_number = temp.serial_number
)
where exists ( select 1
from temp_tt1  temp
where t1.serial_number = temp.serial_number
);-- 约30分钟未完成，取消。
用上述方式更新时，硬盘IO没有规律，与其他方式相比，大部分时间都几乎“没有”IO，极慢。
-- 改为以下方式：
update /*+parallel(t1, 4) nologging*/  tt1 t1 set (occur_cash, occur_unit, cash_balance, balance)
= (
select /*+parallel(temp, 4)*/occur_cash, occur_unit, cash_balance, balance
from temp_tt1  temp
where t1.serial_number = temp.serial_number
)
where exists (
select /*+parallel(temp, 4)*/1
from temp_tt1  temp
where t1.serial_number = temp.serial_number
);-- 无效果，用大表（即使是临时表，本例有365W数据）更新更大的表时，直接用SQL实现在性能上是无法接受的。

commit;
drop table temp_tt1;

 

六、由此想到的：

create table XXX as select .. from ...比插入要快，由此，系统中的临时表如果用create替代insert的话，速度应该有提高。

数据对象的存储及使用设计，应严格根据实际的业务场景来定义，在建立诸如个人流水表之类的大表前，必须对表类型、各种存储结构及参数、是否及如何分区、索引的数量及字段（应根据实际查询要求建立索引）等进行详细的分析。

sql处理的速度还是有上限的，受制于应用环境。

另外，大数据量的处理，还是要考虑拆分为小任务、小事务进行。否则即使数据库有那么大的回滚段，一但后续出错，回滚的时间也等不起，给系统造成的压力也耗不起。

进行上述测试验证，只是好奇而已。