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