1、问题描述
有两张表,A表记录了某些实体的新属性,B表记录了每个实体的旧属性,现在打算用A中的属性值去更新B中相同实体的旧属性,如下图所示:
类似这样的需求,怎样做比较高效呢?
2、制作模拟数据
为了便于说明及进行效率对比,首先我们来制作一些模拟数据。在ORACLE数据库中,模拟数据的制作分如下三步:
- 创建数据表
create table a (tbbh number,dlbm varchar2(3));
create table b (objectid number,tbbh number,dlbm varchar2(3));
- 制作模拟数据,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表更新对应实体的属性值。用一段存储过程来模拟这个问题为:
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、优化解决办法
常规办法是逐条进行更新,那可不可以进行批量的更新呢?答案是肯定的。我们可以这样操作。
- 在A表的TBBH字段上创建主键约束
alter table a add primary key (tbbh) using index;
- 对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 |
很显然,优化后的方法,其性能有较大程度的提升。