本案例发生在 2011 年，当时作者罗老师在惠普担任开发 DBA，支撑宝洁公司的数据仓库项目。为了避免泄露信息，他对 SQL 语句做了适当修改。ETL 开发人员发来邮件问能不能想办法提升一下下面 UPDATE 语句性能，该 UPDATE 执行了 30 分钟还没执行完毕，SQL 语句如下。

    1. UPDATE OPT_ACCT_FDIM A
    2.    SET ACCT_SKID = (SELECT ACCT_SKID
    3.                       FROM OPT_ACCT_FDIM_BKP B
    4.                      WHERE A.ACCT_ID = B.ACCT_ID);

    OPT_ACCT_FDIM 有 226 474 行数据，OPT_ACCT_FDIM_BKP 有 227 817 行数据。UPDATE 后面跟子查询类似嵌套循环，它的算法与标量子查询，Filter 一模一样。也就是说 OPT_ACCT_FDIM 表相当于嵌套循环的驱动表，OPT_ACCT_FDIM_BKP 相当于嵌套循环的被驱动表，那么这里表 OPT_ACCT_FDIM_BKP 就会被扫描 20 多万次。OPT_ACCT_FDIM_BKP 是通过 CTAS 创建的备份表，用来备份 OPT_ACCT_FDIM 表的数据。嵌套循环被驱动表应该走索引，但是 OPT_ACCT_FDIM_BKP 是通过 CTAS 创建的，仅仅用于备份，该表上面没有任何索引，这就是说 OPT_ACCT_FDIM_BKP 要被扫描 20 多万次，而且每次都是全表扫描，这就是为什么 UPDATE 执行了 30 分钟还没执行完毕。我们可以创建一个索引（ACCT_ID，ACCT_SKID）从而避免 OPT_ACCT_FDIM_BKP 每次被全表扫描，虽然这种方法能优化该 SQL，但是此时索引会被扫描 20 多万次。如果要更新的表有几千万行甚至上亿行数据，显然不能通过创建索引的方法来优化 SQL。考虑到 ETL 开发人员后续还有类似需求，笔者决定采用存储过程并且利用 ROWID 对关联更新进行优化。存储过程代码如下。

    1. DECLARE
    2.    CURSOR CUR_B IS
    3.       SELECT 
    4.        B.ACCT_ID, B.ACCT_SKID, A.ROWID ROW_ID
    5.        FROM OPT_ACCT_DIM A, OPT_ACCT_DIM_BKP B
    6.        WHERE A.ACCT_ID = B.ACCT_ID
    7.        ORDER BY A.ROWID;  
    8.      V_COUNTER NUMBER;
    9.   BEGIN
    10.     V_COUNTER := 0;
    11.     FOR ROW_B IN CUR_B LOOP
    12.       UPDATE OPT_ACCT_DIM
    13.         SET ACCT_SKID = ROW_B.ACCT_SKID
    14.        WHERE ROWID = ROW_B.ROW_ID;
    15.       V_COUNTER := V_COUNTER + 1;
    16.       IF (V_COUNTER >= 1000) THEN
    17.         COMMIT;
    18.         V_COUNTER := 0;
    19.       END IF;
    20.     END LOOP;
    21.     COMMIT;
    22. END;
    1. PL/SQL procedure successfully completed.
    3. Elapsed: 00:01:21.58

    将关联更新改写成存储过程，利用 ROWID 进行更新只需要 1 分 22 秒就可执行完毕。当时并没有采用批量游标方式进行更新，如果采用批量游标，速度更快。以下是批量游标的 PLSQL 代码。

    1. declare
    2.   maxrows         number default 100000;
    3.   rowid_table     dbms_sql.urowid_table;
    4.   acct_skid_table dbms_sql.Number_Table;
    5.   cursor cur_update is
    6.     SELECT B.ACCT_SKID, A.ROWID ROW_ID
    7.       FROM OPT_ACCT_DIM A, OPT_ACCT_DIM_BKP B
    8.      WHERE A.ACCT_ID = B.ACCT_ID
    9.      ORDER BY A.ROWID;
    10. begin
    11.   open cur_update;
    12.   loop
    13.     EXIT WHEN cur_update%NOTFOUND;
    14.     FETCH cur_update bulk collect
    15.       into acct_skid_table, rowid_table limit maxrows;
    16.     forall i in 1 .. rowid_table.count
    17.       update OPT_ACCT_DIM
    18.          set acct_skid = acct_skid_table(i)
    19.        where rowid = rowid_table(i);
    20.     commit;
    21.   end loop;
    22.   close cur_update;
    23. end;

    细心的读者会发现，在游标定义中，我们对要更新的表根据 ROWID 进行了排序操作，这是为什么呢？同一个块中 ROWID 是连续的，物理上连续的块组成了区，那么同一个区里面 ROWID 也是连续的。对ROWID进行排序是为了保证在更新表的时候，被更新的块尽量不被刷出buffer cache，从而减少物理I/O。假设要被更新的表有 20GB，数据库的 buffer cache 只有 10GB，这时 buffer cache 不能完全容纳要被更新的表，有部分块会被挤压出 buffer cache。这时如果不对 ROWID 进行排序，被更新的块有可能会被反复读入 buffer cache，然后挤压出 buffer cache，然后重复读入、挤压，此时会引发大量的 I/O 读写操作。假设一个块存储 200 行数据，最极端的情况就是每个块要被读入/写出到磁盘 200 次，这样读取的表就不是 20GB，而是（200×20）GB。如果对 ROWID 进行排序，这样就能保证一个块只需被读入 buffer cache 一次，这样就避免了大量的 I/O 读写操作。有读者会问，排序不也耗费资源吗？这时排序耗费的资源远远低于数据块被反复挤压出 buffer cache 所耗费的开销。如果要被更新的表很小，buffer cache 能完全容纳下要被更新的表，这时就不要对 ROWID 进行排序了，因为 buffer cache 很大，块不会被挤压出 buffer cache，此时对 ROWID 排序反而会影响性能。大家以后遇到类似需求，要先比较被更新的表与 buffer cache 大小，同时也要考虑数据库繁忙程度、buffer cache 还剩余多少空闲块等一系列因素。

    下面实验验证如果不对 ROWID 排序，块有可能被反复扫描的观点。

    我们先创建两个表，分别取名为 a，b，为了模拟实际情况，将 a，b 中数据随机打乱存储。

    1. create table a as select * from dba_objects order by dbms_random.value;
    3. create table b as select * from dba_objects order by dbms_random.value;

    查看返回结果如下。

    1.  select owner,rid as "ROWID",block#
    2.     from (SELECT B.owner,
    3.                  A.ROWID rid,
    4.                  dbms_rowid.rowid_block_number(A.rowid) block#
    5.             FROM A, B
    6.            WHERE A.object_id = B.object_id)
    7.    where rownum <= 10;
    1. OWNER           ROWID                  BLOCK#
    2. --------------- ------------------ ----------
    3. PUBLIC          AAAS+CAAEAACEPdAAs     541661
    4. PUBLIC          AAAS+CAAEAACEp2AAP     543350
    5. SYS             AAAS+CAAEAACEgFAAJ     542725
    6. SYS             AAAS+CAAEAACEu9AAc     543677
    7. MDSYS           AAAS+CAAEAACEknAAi     543015
    8. SYS             AAAS+CAAEAACEutAA9     543661
    9. SYS             AAAS+CAAEAACEhRAA4     542801
    10. SYSMAN          AAAS+CAAEAACEvzAAC     543731
    11. PUBLIC          AAAS+CAAEAACElBAAj     543041
    12. PUBLIC          AAAS+CAAEAACEwUAAy     543764

    从 SQL 查询结果中我们可以看到，返回的数据是无序的。如果关联的两个表连接列本身是有序递增的，比如序列值、时间，这时两表关联返回的结果是部分有序的，可以不用排序，在实际工作中，要具体情况具体分析。

    本案例也可以采用 MERGE INTO 对 UPDATE 子查询进行等价改写。

    1.  merge into OPT_ACCT_FDIM A
    2.   using OPT_ACCT_FDIM_BKP B
    3. on (A.ACCT_ID = B.ACCT_ID)
    4.   when mached then update set a.ACCT_SKID = B.ACCT_SKID;

    MERGE INTO 可以自由控制走嵌套循环或者走 HASH 连接，而且 MERGE INTO 可以开启并行 DML、并行查询，而采用 PLSQL 更新不能开启并行，所以 MERGE INTO 在速度上有优势。PLSQL 更新可以批量提交，对 UNDO 占用小，而 MERGE INTO 要等提交的时候才会释放 UNDO。采用 PLSQL 更新不需要担心进程突然断开连接，MERGE INTO 更新如果进程断开连接会导致 UNDO 很难释放。所以，如果追求更新速度且被更新的表并发量很小，可以考虑采用 MERGE INTO，如果追求安全、平稳，可以采用 PLSQL 更新。