执行计划SQL写法差异改变之insert all
来源:互联网 发布:招行外汇分析软件 编辑:程序博客网 时间:2024/06/05 09:05
/*
性能上来看,insert all不一定会有优势,但是如果分开写和合并写不等价的时候,分开写要很麻烦,比如锁表,
比如中间表,这样性能就要比insert all差多了!
insert all 的执行计划有其关键字:MULTI-TABLE INSERT
*/
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;
set linesize 1000
set pagesize 2000
set autotrace off
ALTER SESSION SET statistics_level = all;
insert into t1 select * from t;
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
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 |
-------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
set linesize 1000
set pagesize 2000
set autotrace off
ALTER SESSION SET statistics_level = all;
insert into t2 select * from t;
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
SQL_ID 0gp802wd8kvj6, child number 0
-------------------------------------
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 |
-------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
---假如T表的数据不会变化,看如下语句(如果T表数据会变化,那上述语句合并成如下是不等价的)
set linesize 1000
set pagesize 2000
set autotrace off
ALTER SESSION SET statistics_level = all;
rollabck;
insert all
into t1
into t2
select * from t;
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));
SQL> SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
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)
已选择18行。
性能上来看,insert all不一定会有优势,但是如果分开写和合并写不等价的时候,分开写要很麻烦,比如锁表,
比如中间表,这样性能就要比insert all差多了!
insert all 的执行计划有其关键字:MULTI-TABLE INSERT
*/
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;
set linesize 1000
set pagesize 2000
set autotrace off
ALTER SESSION SET statistics_level = all;
insert into t1 select * from t;
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
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 |
-------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
set linesize 1000
set pagesize 2000
set autotrace off
ALTER SESSION SET statistics_level = all;
insert into t2 select * from t;
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
SQL_ID 0gp802wd8kvj6, child number 0
-------------------------------------
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 |
-------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
---假如T表的数据不会变化,看如下语句(如果T表数据会变化,那上述语句合并成如下是不等价的)
set linesize 1000
set pagesize 2000
set autotrace off
ALTER SESSION SET statistics_level = all;
rollabck;
insert all
into t1
into t2
select * from t;
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));
SQL> SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
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)
已选择18行。
阅读全文
0 0
- 执行计划SQL写法差异改变之insert all
- 执行计划SQL写法差异改变之with子句
- SQL之执行计划
- 数据库迁移导致sql 执行计划改变
- SQL性能调优之Insert语句干扰了正确的执行计划的一次记录
- sql性能之执行计划
- oracle读懂执行计划之得到sql的执行计划
- 如何使用ordered提示改变SQL执行计划
- Scrips:打印AWR中执行计划的改变awr_plan_change.sql
- 使用hint改变SQL执行计划的方法
- SQL性能分析之执行计划
- MySQL SQL优化之 执行计划 explain
- Oracle执行计划之SQL优化
- Hive 之 SQL执行计划深度解析
- Oracle插入之 insert all、insert first
- ADO方式,VC调用Execute执行INSERT INTO插入变量SQL语句的写法
- 利用DDL改变执行计划
- sql 执行计划
- Pyqt4,选择本地路径,播放一段音频文件
- Win10安装mysql-5.7.20-winx64
- linux命令学习(一)
- MyBatis框架学习2
- 如何修改CentOS6、CentOS7内核支持安装锐速的内核
- 执行计划SQL写法差异改变之insert all
- 习题3(3.3)
- linux环境下安装activeMQ
- 20171026
- Java clone的浅拷贝及深拷贝 详解
- ACE-时间
- poj1837——balance
- HDU 5514 Frogs HDU 4135 Sum (容斥原理的应用)
- Gym 100112DDoorman 模拟—水