Oracle中MERGE UPDATE操作如何避免table1全表扫描并利用索引提速

当前执行计划走全表扫描的核心原因是Oracle优化器评估两张表匹配的行数占table1总比例较高，认为HASH JOIN+全表扫描的成本低于逐行走索引的嵌套循环关联成本。你已经创建的包含id、updated、data的联合索引已经满足覆盖索引要求，不需要调整索引结构的前提下可以通过以下方法调整执行计划。

• 方法1：强制使用嵌套循环关联，引导走table1的索引
  可以通过添加hint指定关联方式，语句修改为：

  MERGE /*+ LEADING(sel t1) USE_NL(t1) */ INTO table1 t1 USING
    (SELECT t2.id ,
            t2.updated ,
            t2.data
     FROM table2 t2) sel ON (sel.id = t1.id
                             AND sel.updated = t1.updated) WHEN MATCHED THEN
  UPDATE
  SET t1.data = sel.data;
  

  这里LEADING hint指定先扫描sel也就是table2的结果集，USE_NL指定和table1关联时走嵌套循环，每一行匹配时都会走table1的（id,updated）联合索引，完全避免全表扫描。这个方案适合两张表匹配行数占table1总行数比例低于10%的场景，性能提升会非常明显。
• 方法2：调整table1联合索引的列顺序，优化索引匹配效率
  如果你当前table1的联合索引列顺序不是id在前、updated在后，调整为(id, updated, data)的顺序，这个索引完全覆盖MERGE操作需要的所有字段，不需要回表，走索引的成本会进一步降低，优化器更倾向于自动选择走索引而不是全表扫描。
• 方法3：收集最新的统计信息，修正优化器评估偏差
  如果统计信息过时，优化器可能错误判断匹配行数占比，导致错误选择全表扫描。执行以下命令更新两张表的统计信息：

  EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => '你的schema名', tabname => 'TABLE1', cascade => TRUE);
  EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => '你的schema名', tabname => 'TABLE2', cascade => TRUE);
  

  统计信息更新后优化器会重新评估成本，如果实际匹配行数占比低，会自动选择走索引访问table1。

如果实际匹配行数占table1的比例超过20%，全表扫描+HASH JOIN的性能其实会比走嵌套循环索引访问更高，这种场景不需要强制调整执行计划。

内容的提问来源于stack exchange，提问作者Gorkov Aleksey