ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【原创】验证在Oracle数据库中操纵数据的各种方法的速度

【原创】验证在Oracle数据库中操纵数据的各种方法的速度

原创 Linux操作系统 作者:yujj_cn 时间:2012-03-19 22:14:47 0 删除 编辑

机器配置:组装的PC SERVER4AMD CPU8G 内存,4SCSI硬盘。Oracle 10.2.0.4

 

试验目的:244上,对xxx_flow表进行复制、更新,验证在Oracle数据库中操纵数据的各种方法的速度。共35,629,784条记录。

 

直接建表:

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

  结果:

  第一次:126.297秒,约每秒写入28.2W条数据。

  第二次:149.109秒,约每秒写入23.9W条数据。

 

使用BULK COLLECT批量处理:

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

 

-- 以下代码还有一个可以参考的地方:没有根据表结构声明太多的类型及变量。

declare

  cursor cur_temp is

    select /*+parallel(pf, 4)*/* from xxx_flow pf;

   

  type t_person_flow is table of 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;

 

  close cur_temp;

end;

 

drop table tt1;

  结果:

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

  第二次:505.953秒。约每秒写入7.0W条数据。(hint,把limit200改为1000)

  如果在目标表上建立索引后再插入,哪怕只建立一个主键约束,耗时也将*2还多。

 

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

 

更新10%20%50%100%的数据:

  /*

   建表并建立索引

  */

  create /*+parallel*/table tt1 as select /*+parallel(pf, 4)*/ * from xxx_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 table of 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;

 

  close cur_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;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26802046/viewspace-719059/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2012-03-19

  • 博文量
    8
  • 访问量
    37280