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

本文链接：https://blog.csdn.net/weixin_39534833/article/details/148114004

梁敬彬梁敬弘兄弟出品

往期回顾
左右SQL执行计划妙招 ①——子查询的应用范围
 左右SQL执行计划妙招 ②——Hint无效原因
 左右SQL执行计划妙招 ③ ——执行计划SQL写法差异改变【with子句】

前言

在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)