讨论更新大表某个字段方式

    我们讨论下更新方式的话题，在河北项目中曾经碰到过这样的一个问题：我们需要更新SB_ZZS_2003_FB5的HY_ID，通过关联DIM_NSR的NSRDZDAH,然后进行更新HY_ID。说明下当时SB_ZZS_2003_FB5是张二级分区表，大约28千万条记录，20G上下的容量；DIM_NSR大约在120万条记录左右。

    我第一反应是做了一张临时表记从dim_nsr中取nsrdzdah以及hy_id这二个字段，然后对这二个字段建立个复合索引， SQL语句是：

Update SB_ZZS_2003_FB5 fb set fb.hy_id=(select hy_id from dim_nsr dim where fb.nsrdzah=dim.nsrdzdah) where exists (select 1  from dim_nsr dim where fb.nsrdzah=dim.nsrdzdah);

Commit;

原本以为可以很快的执行完的，但是事实上执行了几个小时一点动静都没有。然后进行第一次改进，思路是减少扫描SB_ZZS_2003_FB5的次数，把SQL语句改写成：

Update SB_ZZS_2003_FB5 fb set fb.hy_id=nvl((select hy_id from dim_nsr dim where fb.nsrdzah=dim.nsrdzdah)，fb.hy);

Commit;

这样修改之后，事实上确实比第一种方式速度来着快，但是执行几个小时仍旧没有一点反应。

然后去网上找资料以及问同事，基本上形成2种不同的思路。

第一种思路：

观察整张表不仅记录多，而且容量也大。由于这张表做过分区表，切入点是对700多个小分区，一个个的更新。化整为零的方式，因为每个小分区的数量很少大约在100W以内（小机处理100W以内的更新还是很轻松的）。然后把SQL进行改写：

Update SB_ZZS_2003_FB5 SUBPARTITION(PART_04_ZQ01) fb set fb.hy_id=nvl((select hy_id from dim_nsr dim where fb.nsrdzah=dim.nsrdzdah)，fb.hy);

Commit;

当然700多个小分区不能一个个进行手写，需要利用系统视图进行拼接SQL语句，这样就可以做成一个脚本。

第二种思路：利用游标的方式进行把千万条记录进行逐步更新，采用每更新完1000条记录提交，边更新边提交，这种方式的时间是可以计算出来的，速度也是非常稳定。这种方式的SQL写法如下：

DECLARE

  CURSOR cur IS

    select s.hy_id, z.rowid ROW_ID

      From nsr_shulm s,  SB_ZZS_2003_FB5 z

     where s.nsrdzdah = z.nsrdzdah

     order by z.rowid; ---如果表的数据量不是很大,可以不用 order by rowid

 

  V_COUNTER NUMBER;

BEGIN

  V_COUNTER := 0;

  FOR row IN cur LOOP

      UPDATE SB_ZZS_2003_FB1 SET hy_id = row.hy_id WHERE ROWID = row.ROW_ID;

    V_COUNTER := V_COUNTER + 1;

    IF (V_COUNTER >= 1000) THEN

      COMMIT;

      V_COUNTER := 0;

    END IF;

  END LOOP;

  COMMIT;

END;

 

接下来分析这二种新思路和传统写法：

传统的写法：这种方式一定要全部更新完了之后才能写入数据库，对于这样大的表你一时半刻不能更新完，但是新的数据晚上会写进来，这样就会产生锁，影响到其他工作。第二个缺点是万一SQL执行到一半出错，那么之前执行的全部回滚掉了。

第一种思路：这种思路优点是可以执行一个分区直接提交，推进的速度也很匀速。

第二种思路：这是oracle官方提供的更快更新方式，利用rowid与hy_id相关联之后去更新SB_ZZS_2003_FB5,每更新1000条提交一次，更新速度也能保证。

在实践中传统思路是根本没有办法操作这类大表的。对于新的二种思路在实践中表明都可以正常更新，并且不妨碍日常的正常抽取工作，但是在效率上仍旧是个瓶颈。

 

最后参考一种具有爆发力的方法：

更新字段的原因是：后期发现的问题，换个角度思考的话，你可以重新初始化这张表，在某种意义上来讲，速度更快。

前提是数据库服务器性能很好（小机的资源空闲），利用oracle的索引，充分利用oracle的并发，在这三个条件下可以在小时级别里面完成你要更新的字段。这种方法要做到，第一个是你需要进行对你的SQL语句进行优化（看执行计划）；第二个你需要建立合适的索引来为你修改字段服务；第三个你要合理的采用并发机制加快你的运行的速度。

         在河北实践数据：大约8000W条数据的表用第一二种方式用了2个小时左右；但是更新28000W条数据的时候就进行的很慢，然后采用了第重新抽取，用了6个小时上下。

    注意点：随着数据量的成倍增长，更新的时间并非按照成倍的方式增长，而是成几倍或者指数级别的增长。