执行计划SQL写法差异改变之insert all

最新推荐文章于 2022-10-21 11:23:50 发布

一个阳光努力的程序盐

最新推荐文章于 2022-10-21 11:23:50 发布

阅读量499

点赞数

分类专栏： Oracle之hints

Oracle之hints 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

/*
性能上来看，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行。