大数据量 SQL update语句优化

有两张1.5亿数据量的表,一张表中有字段name,大概有5000万左右为null,另一张表name字段不为null,两张表可通过主键id关联。现在省略去不必要的字段,以及分区等构造两张实验表:

SQL> CREATE TABLE p_t AS SELECT ROWNUM ID,CASE WHEN MOD(ROWNUM,3)=0 THEN NULL ELSE DBMS_RANDOM.STRING('a',20) END NAME
  2  FROM DUAL CONNECT BY ROWNUM<=1000000;
Table created.
SQL> CREATE TABLE c_t AS SELECT ID,nvl(name,DBMS_RANDOM.STRING('a',20)) name
  2  FROM p_t;
Table created. 
SQL> create index idx_p on p_t(id);
Index created.
SQL> create index idx_c on c_t(id);
Index created.

SQL> exec dbms_stats.gather_table_stats(user,'C_T',cascade=>true,degree=>10);
PL/SQL procedure successfully completed..
SQL> exec dbms_stats.gather_table_stats(user,'C_T',degree=>6);
PL/SQL procedure successfully completed.
SQL> alter table P_t noparallel;
Table altered.
SQL> alter table c_t noparallel;
Table altered.
SQL> select count(1) from p_t where name  is null;
  COUNT(1)
----------
    333333

同事给我的sql类似于以下sql,说更新5000多万数据执行两个小时都执行不完,先来看下语句和执行计划:
update p_t set name=(select name from c_t where p_t.id=c_t.id) where p_t.name is null;
234836pkyz1qeyeff5jfyp.png 
我拿到sql一看是update语句,想都没多想直接加并行,但是加了并行发现子查询根本没有用到并行,而且整体cost没有什么变化。我仔细想了想,这种写法oracle内部是通过单条跟新的方式进行,从p_t中那出一条记录去c_t中匹配,c_t中采用索引扫描,然后再通过rowid访问表取到name。所以优化器计算后,不会对子查询中的c_t应用并行,因为此时并行会采用全表扫描,效率肯定没有索引扫描快。语句和执行计划如下:
update /*+parallel(p_t,4)*/ p_t set name=(select /*+parallel(c_t,4)*/ name from c_t where p_t.id=c_t.id) where p_t.name is null;
23483608gg8z7g9rrnatge.png 
其实子查询中通过id关联实际上类似于相关子查询,想到数据量,如果能够将两张表通过全表扫描,并用hash join关联应该是最快的。这时候想到了update内联视图或者采用merge两种方法。因为实际情况中p_t与c_t关联会产生重复数据,所以不能用update内联视图的方式,最终采用merge方式。实验环境中我采用两种方法进行优化,为了对比准确,没有加并行。虽然这样更新了整个p_t表,但是只扫描了c_t表一次。
SQL> merge into p_t using c_t
  2  on (c_t.id=p_t.id)
  3  when matched then
4 update set
5 p_t.name = nvl2(p_t.name,c_t.name,p_t.name);        
1000000 rows merged.
Elapsed: 00:00:18.77
000521n4y5dnmf9vt491v2.png 

SQL> alter table p_t add primary key(id);
Table altered.
SQL> alter table c_t add primary key(id);
Table altered.
SQL> update (select p_t.name name1,c_t.name name2,p_t.id pid,c_t.id cid  from p_t,c_t where p_t.id=c_t.id) t
  2  set name1=nvl2(name1,name2,name1);
1000000 rows updated.
Elapsed: 00:00:20.14
0005216g0jo0su4ucdctt5.png 

在使用update内联视图优化时,一定要注意必须通过主键等确保惟一性的条件进行关联更新,不然会报错:
ORA-01779: cannot modify a column which maps to a non key-preserved table

回来上网查了下,网上说还可以通过快速游标进行更新,但我觉得肯定没有一条sql语句块。无论pl/sql再怎么优化,本质上无法避免单条更新和引擎切换带来的消耗。实验如下:
SQL> begin
  2  for v_i in (select p_t.rowid,c_t.name from p_t,c_t where c_t.id=p_t.id) loop
  3    update p_t set name=v_i.name where rowid=v_i.rowid;  
4 end loop;
5 end;        
  6  /
PL/SQL procedure successfully completed.
Elapsed: 00:01:23.05

实际应用中,我将语句改写成了merge,并加入了dml并行(SQL> alter session enable parallel dml;)和select并行提示,将原来更新两个多小时的sql优化到只跟新了3分钟不到。Oracle Parallel Execution(并行执行)
总结:update的优化如果可以改写成merge或者update内联视图的方式,适用于大数据量更新,避免采用单条更新,防止多次扫描子查询中的表。资源允许情况,可以开启dml和查询并行。update内联视图要注意必须采用主键等能确保唯一条件进行关联,改写时要保持逻辑一致。

转载于:https://my.oschina.net/ljc94/blog/1588764

  • 1
    点赞
  • 0
    评论
  • 5
    收藏
  • 扫一扫,分享海报

参与评论 您还未登录,请先 登录 后发表或查看评论
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值