(这是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处理的速度还是有上限的,受制于应用环境。
另外,大数据量的处理,还是要考虑拆分为小任务、小事务进行。否则即使数据库有那么大的回滚段,一但后续出错,回滚的时间也等不起,给系统造成的压力也耗不起。
进行上述测试验证,只是好奇而已。
相关推荐
第一部分 ORACLE系统优化基本知识 23 第1章 ORACLE结构回顾 23 §1.1 Oracle数据库结构 23 §1.1.1 Oracle数据字典 23 §1.1.2 表空间与数据文件 24 §1.1.3 Oracle实例(Instance) 24 §1.2 Oracle文件 26 §1.2.1...
Model进行处理,页面调转由STRUTS的控制层Controller实现,页面负责显示请求和响应,大大增强了“人机”的互动性,这一层在 STRUTS的视图层View用STRUTS标签+HTML等实现,同时使用validator验证框架进行数据检验。...
源代码部分涵盖了构建该系统所需的所有关键组件,包括商品展示、购物车管理、订单处理、用户验证等模块。这些代码片段不仅展示了如何利用JSP技术进行动态网页设计,还演示了后端数据库的交互,如MySQL或Oracle,以...
其方法是由系统为用户提供一定的方式和规则 让用户标识自己的名字或身份,以便每次用户登陆系统时,对比数据库记录的合法用户 信息,通过验证方能访问数据库。具体设计数据库时我们可以创建sys_user表,这些用 户的...
这里面实现了很多的帮助方法,比如正则验证,加密,解密,MD5加密,字符串的处理等操作。 2.最新的 PageValidate 类 主要是实现了验证,是否为空,是否为数字等。 3.JavascriptHelp 帮助输出简单的JS代码 4.最新...
处理速度快(Velocity),实时分析对某些应用才更有意义,而不是批量式分析,即时处理已经成为趋势之一。 大数据的发展趋势和带来的机会 在大数据概念出来之前,个人制造的数据往往被忽略,企业数据被谈及的更多。...
利用数据库具有稳定性、安全性高、运行速度快等优点,由管理员将学生信息输入到数据库,这样的数据安全、永久、稳定、存储数据量巨大、可扩展性强,并且能够很快的查询,修改,删除学生记录等。宿舍管理员只需要掌握...
13、在数据库中查询语句速度很慢,如何优化? 44 14、数据库三范式是什么? 44 15、union和union all有什么不同? 45 16、char、varchar2、varchar有什么区别? 45 17、Oracle和Mysql的区别? 46 18、Oracle语句有多少...
业务层是Web应用的中间部分,使业务逻辑和数据库持久操作解耦,使代码更容易维护,主要处理应用程序的业务逻辑和业务验证,管理事务,预留和其它层交互的接口,管理业务层对象之间的依赖,增加在表现层和持久层...
业务层是Web应用的中间部分,使业务逻辑和数据库持久操作解耦,使代码更容易维护,主要处理应用程序的业务逻辑和业务验证,管理事务,预留和其它层交互的接口,管理业务层对象之间的依赖,增加在表现层和持久层...
在ASP.NET页面中推荐使用覆写(Override)而不是事件处理(Event Handler) 常用编码工具类,支持base64,md5,des,crc32 也谈谈技术面试 在C#里把ArrayList转换为Array 或 把Array转换为ArrayList C# 2.0 在.NET 2.0中...
PHP网页文件被当作一般HTML网页文件来处理并且在编辑时你可以用编辑HTML的常规方法编写PHP。 PHP代表:超文本预处理器(PHP: Hypertext Preprocessor)。PHP是完全免费的,不用花钱,你可以从PHP官方站点...
02、支持Microsoft SQL Server、Microsoft Access、Oracle、MySql、Excel、FoxPro、FoxBase、Text等数据库连接 03、支持从数据表、视图(Access为查询)中读取数据字段 04、支持通过数据表中的组合主键传递参数 05、...
02、支持Microsoft SQL Server、Microsoft Access、Oracle、MySql、Excel、FoxPro、FoxBase、Text等数据库连接 03、支持从数据表、视图(Access为查询)中读取数据字段 04、支持通过数据表中的组合主键传递参数 05、...
启用重影处理日志记录 101 将重影日志记录配置为记录到中央文件中 101 启用 Windows 事件日志的重影日志记录 101 通过策略启用用户对用户重影操作 102 创建用户策略以定义可执行重影操作的用户 102 示例: 为用户对...
02、支持Microsoft SQL Server、Microsoft Access、Oracle、MySql、Excel、FoxPro、FoxBase、Text等数据库连接 03、支持从数据表、视图(Access为查询)中读取数据字段 04、支持通过数据表中的组合主键传递参数 05、...
技术上的可行性分析要考虑将来要采用的硬件和软件技术能否满足用户(这里是服务器,网速)提出的要求(如计算机的容量、速度等)。此外,还要考虑开发人员的水平,学习了两年的jsp开发,对于这个系统的编写,我想...
02、支持Microsoft SQL Server、Microsoft Access、Oracle、MySql、Excel、FoxPro、FoxBase、Text等数据库连接 03、支持从数据表、视图(Access为查询)中读取数据字段 04、支持通过数据表中的组合主键传递参数 05、...
它最大的特点就是简单易用,访问速度快。使用Toad,我们可以通过一个图形化的用户界面快速访问数据库,完成复杂的SQL和PL/SQL代码编辑和测试工作。Toad由Oracle开发专家专门为开发人员而设计,是一个功能强大、结构...
02、支持Microsoft SQL Server、Microsoft Access、Oracle、MySql、Excel、FoxPro、FoxBase、Text等数据库连接 03、支持从数据表、视图(Access为查询)中读取数据字段 04、支持通过数据表中的组合主键传递参数 05、...