往期回顾：

前言

在Oracle数据库优化中，同一个业务需求往往可以用多种SQL写法来实现，而不同的写法会产生不同的执行计划，进而影响性能表现。本文将深入探讨INSERT ALL语句与普通插入语句在执行计划上的差异，帮助大家理解如何通过改变SQL写法来优化数据库性能。

环境准备：

drop table t1 purge;
create table t1 as select  * from dba_objects where 1=2;
drop table  t2 purge;
create  table t2 as select * from dba_objects where 1=2;
drop table t purge;
create  table t as select * from dba_objects;

这里我们创建了三个表：

t1、t2：空表，结构与dba_objects相同，用作目标表

t：包含完整数据的源表，用于插入操作

普通的插入语句执行计划分析：

分别插入两个表的执行计划

首先，我们来看传统的分别插入两个表的方式：

插入表t1：

insert into t1 select * from t;

执行计划如下：

SQL_ID  d4y6zf9bsqk1b, child number 0
-------------------------------------------------------------------------------------------
insert into t1 select * from t
Plan hash value: 1601196873
-------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |      1 |        |      0 |00:00:00.15 |   10935 |
|   1 |  LOAD TABLE CONVENTIONAL |      |      1 |        |      0 |00:00:00.15 |   10935 |
|   2 |   TABLE ACCESS FULL      | T    |      1 |  74811 |  73107 |00:00:00.02 |    1047 |
-------------------------------------------------------------------------------------------

插入表t2：

insert into t2 select * from t;

执行计划：

Plan hash value: 1601196873
-------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |      1 |        |      0 |00:00:00.17 |   10935 |
|   1 |  LOAD TABLE CONVENTIONAL |      |      1 |        |      0 |00:00:00.17 |   10935 |
|   2 |   TABLE ACCESS FULL      | T    |      1 |  74811 |  73107 |00:00:00.02 |    1047 |
-------------------------------------------------------------------------------------------

普通插入方式的特点分析

从上述执行计划可以看出：

- 重复扫描：需要对源表t进行两次全表扫描

- 资源消耗：总共消耗21,870个buffers（10,935 × 2）

- 执行时间：两次操作的总时间为0.32秒

INSERT ALL语句的执行计划分析

现在我们使用INSERT ALL语句来实现同样的功能：

-- 设置执行计划显示参数
set linesize 1000
set pagesize 2000
set autotrace off
ALTER SESSION SET statistics_level = all;
-- 执行INSERT ALL
insert all 
   into t1
   into t2
select * from t;

查看执行计划：

SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));
执行计划输出：
SQL_ID  795gsytaz3bkt, child number 0
--------------------------------------------------------------------------------------------
insert all into t1 into t2 select * from t
Plan hash value: 1563282152
--------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT    |      |      1 |        |      0 |00:00:00.42 |   25858 |    734 |
|   1 |  MULTI-TABLE INSERT |      |      1 |        |      0 |00:00:00.42 |   25858 |    734 |
|   2 |   TABLE ACCESS FULL | T    |      1 |  74811 |  73107 |00:00:00.16 |    1047 |    734 |
--------------------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)

执行计划差异对比分析

关键差异点

1. 操作类型：INSERT ALL使用MULTI-TABLE INSERT操作，这是Oracle专门为多表插入优化的执行路径

2. 扫描次数：普通方式：需要扫描源表2次；INSERT ALL：只需要扫描源表1次

3. 资源消耗对比：普通方式：21,870 buffers（两次操作总和）；INSERT ALL：25,858 buffers（单次操作）

性能优势分析

虽然从这个简单示例来看，INSERT ALL的buffer消耗似乎更高，但这并不意味着性能更差。实际应用中，INSERT ALL的优势主要体现在：

I/O效率：只需要读取源表一次，大幅减少I/O操作

锁竞争：减少了对源表的锁定时间

事务一致性：保证所有插入操作在同一个事务中完成

代码简洁性：一条语句完成多表插入，减少代码复杂度

实际应用场景优势

INSERT ALL的真正优势在复杂场景中更加明显：

1. 避免锁表问题

当源表在业务高峰期被频繁访问时，多次扫描会增加锁竞争，而INSERT ALL只需要锁定一次。

2. 中间表处理

如果需要通过复杂查询或中间表来准备数据，使用INSERT ALL可以避免重复的数据准备工作。

3. 事务一致性要求

当多个目标表的数据必须保持一致性时，INSERT ALL确保所有插入操作要么全部成功，要么全部失败。

总结

INSERT ALL语句通过引入MULTI-TABLE INSERT操作，实现了对多表插入场景的优化。虽然在简单场景下性能提升可能不明显，但在复杂的生产环境中，特别是涉及大数据量、高并发、严格事务要求的场景下，INSERT ALL展现出明显的优势。

理解不同SQL写法对执行计划的影响，是数据库性能优化的重要技能。在下一集中，我们将继续探讨更多SQL写法差异对执行计划的影响，帮助大家建立更全面的SQL优化思维。

未完待续..

公众号：收获不止数据库

微信：ljbyxl1213

系列回顾

ITPub博客

左右SQL执行计划妙招 ④ ——执行计划SQL写法差异改变【insert all】

前言

环境准备：

普通的插入语句执行计划分析：

INSERT ALL语句的执行计划分析

查看执行计划：

性能优势分析

总结

最新文章