1、问题描述

有两张表,A表记录了某些实体的新属性,B表记录了每个实体的旧属性,现在打算用A中的属性值去更新B中相同实体的旧属性,如下图所示:

以A表中的值快速更新B表中记录的方法-LMLPHP

类似这样的需求,怎样做比较高效呢?

2、制作模拟数据

    为了便于说明及进行效率对比,首先我们来制作一些模拟数据。在ORACLE数据库中,模拟数据的制作分如下三步:

  1. 创建数据表

    create table a (tbbh number,dlbm varchar2(3));

    create table b (objectid number,tbbh number,dlbm varchar2(3));

  2. 制作模拟数据,A表插入10000行记录,B表插入100000行记录

    insert into a select rownum tbbh ,dbms_random.string('U',3) from dual connect by level <10000;

    insert into b select rownum objectid ,rownum tbbh ,dbms_random.string('U',3) from dual connect by level <100000;

    commit;

  3. 查看实体的原始属性值与目标属性值

    以A表中的值快速更新B表中记录的方法-LMLPHP

3、常规解决办法

常规解决思路:从A表中每读出一条记录,去B表更新对应实体的属性值。用一段存储过程来模拟这个问题为:

begin

for x in (select tbbh,dlbm from a)

loop

update b set b.dlbm=x.dlbm where b.tbbh=x.tbbh;

end loop;

commit;

end;

在B表TBBH字段未创建索引的情况下,耗时约17.95s。

在B表TBBH字段创建索引的情况下,耗时约1.18s。

4、优化解决办法

常规办法是逐条进行更新,那可不可以进行批量的更新呢?答案是肯定的。我们可以这样操作。

  1. 在A表的TBBH字段上创建主键约束

    alter table a add primary key (tbbh) using index;

  2. 对A、B表的联合视图进行更新

    update (select b.tbbh ,a.dlbm adlbm ,b.dlbm bdlbm from a,b where a.tbbh=b.tbbh ) set bdlbm=adlbm;

    使用该方法,在B表TBBH字段未创建索引的情况下,耗时约0.96s。

    使用该方法,在B表TBBH字段创建索引的情况下,耗时约0.54s。

5、效率对比

 

B表TBBH无索引

B表TBBH有索引

常规方法

17.95s

1.18s

优化方法

0.96s

0.54s

很显然,优化后的方法,其性能有较大程度的提升。

05-11 16:55