ORACLE大表关联更新提速方法

谁借莪１个温暖的怀抱￠ 2022-02-14 16:57 806阅读 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关联快很多倍。

### 测试结果及源码 ###

有了以上思路，可以通过存储过程代替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);

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

create or replace procedure p\_test\_update

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;

### 限制 ###

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

[image]: /images/20220214/c1b1b80941db4aee9ef865ab2674bec5.png
[image 1]: /images/20220214/95ed2353553540ff95aac7cd962ac643.png
[image 2]: /images/20220214/af0a5dc3a0ba400092255f62cf43a981.png