阿里云优惠
 汇集各种优惠信息 技术资料

首页    数据库    ORACLE大表关联更新怎么提速?

ORACLE大表关联更新怎么提速?

创建时间:2019-04-17 09:09
浏览量:0
收藏

前提知识

在oracle数据库中,每一行记录都有一个该记录的唯一标识rowid,rowid一旦确定不会随意变动。rowid由10个字节存储,在数据库查询中显示为18位的字符串,在其内部存储以下信息:1.对象编号。2.记录所在数据文件。3.记录所在文件上块的编号。4.记录所在块的行编号。

在两表的关联更新时,一般都会在表上建立索引。在表上建立索引时,oracle会根据索引字段的内容(key)和该行的rowid(value)建立一个B-tree,一般为三层,达到4层时会影响索引性能。当执行查询时,先根据关键字段找到对应的rowid,再根据rowid到磁盘中找到记录的位置将记录查询出来。

大表关联更新的瓶颈

执行两表关联更新时,oracle一般采用类似nested_loop的更新方式。当依据关联关系将表B的值更新到A表的字段中时,先遍历A表的,对A表的每行记录查询B表的索引,得到b表的结果后再更新到A表。由于存在查询索引的操作,更新每条记录都会至少执行两次io操作。第一次查询索引、第二次根据索引的rowid查询数据。当执行大规模数据更新时,速度会很慢。

性能瓶颈突破思路

所谓“成也萧何败萧何”,既然在大规模的数据更新中索引存在性能瓶颈,那就想办法在大规模数据更新时避免索引的使用。由于rowid是记录的唯一标识且根据rowid去更新时会自动定位记录所在位置(比通过索引更快),可以考虑将rowid和要更新的结果批量查询出来再批量去更新。批量查询时,oracle一般会采用hash关联的方式。在两表数据量比较大时,hash关联比通过索引nested_loop关联快很多倍。

附:申请阿里云服务器等产品时,可以使用1888元阿里云代金券,阿里云官网领取网址: https://promotion.aliyun.com/ntms/yunparter/invite.html?userCode=2a7uv47d 新老用户均可使用。

测试结果及源码

有了以上思路,可以通过存储过程代替update语句实现更新。在存储过程中可以暂时保存批量查询的结果,依据批量查询的结果执行更新。后面的测试结果是我在虚拟机上跑出来的,性能差异不明显。如果是物理机性能差距会更大。

首先建两张表并造一些测试数据
drop table test_user purge;

create table test_user

( user_id number(11),

user_name varchar2(64),

user_acct number(11)

) tablespace tbs_all_in_one;

drop table test_relations purge;

create table test_relations

(

user_id number(11),

user_acct number(11),

start_date date,

end_date date

)tablespace tbs_all_in_one;

insert into test_user values (NULL,NULL,NULL);
commit;

--造200w条A表数据

set serveroutput on;

begin

for i in 1..21 loop

  insert into test_user select * from test_user;

  commit;

  dbms_output.put_line('insert loop: '||i);

end loop;

end;

/

select count(*) from test_user;

--根据rownum更新A表的user_id

update test_user a

set a.user_id=rownum+100000000,

   a.user_name='user_name'||to_char(rownum+100000000),

   a.user_acct = 0;

commit;

--造400w条B表数据

insert into test_relations

select user_id,user_id+200000000,sysdate-365,sysdate-30-1/86400

from test_user;

commit;

insert into test_relations

select user_id,user_id+200000000,sysdate-30, sysdate+365

from test_user;

commit;

然后建立索引,并执行oracle的统计命令,统计两个表的信息

create index indx_test_user on test_user(user_id) tablespace tbs_all_in_one;

create index indx_test_relations on test_relations(user_id) tablespace tbs_all_in_one;

exec dbms_stats.gather_table_stats(OWNNAME=>'luhao',tabname=>'test_user',cascade=>true);

exec dbms_stats.gather_table_stats(OWNNAME=>'luhao',tabname=>'test_relations',cascade=>true);

普通更新语句的执行计划和时间
普通更新
存储过程更新方法
存储过程
分析上图的源码截图,先通过大表关联得到A表中的rowid和要更新的结果。将数据放到数组中,再通过forall语法根据rowid批量更新。
存储过程更新时间
存储过程更新时间
存储过程源码

create or replace procedure p_test_update

as

iv_sql VARCHAR2(4000) ;

TYPE tab_acct IS TABLE OF NUMBER(11) INDEX BY binary_integer;

TYPE ref_cur IS REF CURSOR;

rowid_arry dbms_sql.urowid_table;

acct_array tab_acct;

iv_cur ref_cur;

start_time date;

finish_time date;

begin

select sysdate into start_time from dual;

iv_sql := 'select a.rowid, b.user_acct from test_user a, test_relations b

where a.user_id = b.user_id and sysdate between b.start_date and b.end_date';

open iv_cur for iv_sql;

loop

  fetch iv_cur bulk collect into rowid_arry,acct_array limit 10000;

  exit when rowid_arry.count = 0;

  --更新acct

  forall i in 1..rowid_arry.count

     update test_user a

        set a.user_acct = acct_array(i)

      where rowid = rowid_arry(i);

end loop;

close iv_cur;

commit;

select sysdate into finish_time from dual;

dbms_output.put_line('cost seconds:'||(finish_time-start_time)*86400);

end;

限制

只能更新静态数据,不能支持事务。

免费领取阿里云1888元代金券大礼包

 

阿里云新老用户均可领取!
自领取后:限时7天使用!

阿里云服务器2折优惠:低至293元/年

 

 

突发性能实例t5 1核1G:293元/年

突发性能实例t5 1核2G:459元/年

突发性能实例t5 2核4G:798元/年

共享型xn4实例1核1G内存:394元/年

共享型n4实例1核2G内存:653元/年

计算网络增强型实例2核4G内存:1566元/年

计算网络增强型实例4核8G内存:2991元/年

点此查看2折活动详情

阿里云高性能云服务器

 

 

网络增强型云服务器:2核4G ¥720元/年

高频应用云服务器:8核16G ¥4109元/年

本地SSD型云服务器:4核16G ¥6218.40元/年

大数据型云服务器:8核32G ¥11375.00元/年

GPU异构云服务器:16核40G ¥15563.00元/年

新用户满立减:每满1000立减50

 

1、到阿里云官网选购产品
2、加入到购物车
3、结算时立享满减

注意:新用户首次购买时必须先加到购物车,然后一起结算才享受此优惠。

腾讯云CVM云服务器22.07元起

 

 

腾讯云1核1G:22.07元/月、794.73元/3年

腾讯云2核2G:36.48元/月、1313.35元/3年

腾讯云2核4G:43.01元/月、1548.5元/3年

腾讯云4核8G:178.5元/月、6426元/3年