第一种：create table s as select * from dba_objects;
第二种：create table s as select * from dba_objects where 1=0;
insert /*+ append */ into s select * from dba_objects;
第三种：create table s as select * from dba_objects where 1=0;
insert into s select * from dba_objects;
第一种建表方式产生的REDO量是79648
SQL> select a.name,b.value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 644392
已用时间: 00: 00: 00.01
SQL> create table s as select * from dba_objects;
表已创建。
已用时间: 00: 00: 00.57
SQL> select a.name,b.value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 724040
已用时间: 00: 00: 00.00
SQL> select 724040-644392 from dual;
724040-644392
-------------
79648
2）第二种建表产生的REDO量是18528+2304= 20832
SQL> select a.name,b.value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 502228
已用时间: 00: 00: 00.01
SQL> create table s as select * from dba_objects where 1=0;
表已创建。
已用时间: 00: 00: 00.11
SQL> select a.name,b.value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 520756
已用时间: 00: 00: 00.01
SQL> select 520756-502228 from dual;
520756-502228
-------------
18528
已用时间: 00: 00: 00.01
SQL> select a.name,b.value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 930940
已用时间: 00: 00: 00.01
SQL> insert /*+ append */ into s select * from dba_objects;
已创建49900行。
已用时间: 00: 00: 00.60
SQL> select a.name,b.value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 933244
已用时间: 00: 00: 00.01
SQL> select 933244-930940 from dual;
933244-930940
-------------
2304
第三种建表产生的redo量是5658116
SQL> select a.name,b.value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 0
已用时间: 00: 00: 00.01
SQL> create table s as select * from dba_objects where 1=0;
表已创建。
已用时间: 00: 00: 00.22
SQL> insert into s select * from dba_objects;
已创建49900行。
已用时间: 00: 00: 00.62
SQL> select a.name,b.value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 5658116
已用时间: 00: 00: 00.01
从实验结果看第二种建表方式产生的REDO两最少。第一种次之，第三种产生的日志量最多。
第二种采用直接路径插入，几乎不产生日志，理解。
第三种常规路径插入，产生日志，理解。
那第一种呢，后台不知道执行的是个什么操作？
从10046跟踪文件能够看出第一种与第二种的本质是一样的，都是采用的直接路径加载。每种方法测试前都先执行以下语句：
SQL> alter system flush buffer_cache;
用tkprof格式化跟踪文件，并按照响应时间排序，这里仅截取重要部分，其他部分略。
C:\>tkprof D:\oracle\product\10.2.0\admin\rose\udump\rose_ora_3752_wxh.trc D:\oracle\product\10.2.0\admin\rose\udump\wxh.log sort=prsela,exeela,fchela
第一种建表方式的跟踪文件输出：
create table s as select * from dba_objects
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 1.27 3.29 889 5213 1280 49900
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.28 3.30 889 5213 1280 49900
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
control file sequential read 5 0.01 0.01
db file sequential read 282 0.06 0.83
db file scattered read 78 0.14 0.95
direct path write 5 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
*****************************************************************************
第二种建表方式跟踪文件输出：
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.05 0.04 0 0 0 0
Execute 1 1.39 2.99 890 5351 1253 49900
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.44 3.04 890 5351 1253 49900
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
control file sequential read 5 0.01 0.05
db file sequential read 283 0.06 0.78
db file scattered read 78 0.06 0.72
direct path write 5 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
*****************************************************************************
第三种建表方式跟踪文件输出：
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 0 0 0 0
Execute 1 1.35 2.76 886 6273 6821 49900
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.37 2.78 886 6273 6821 49900
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 282 0.11 0.75
db file scattered read 78 0.05 0.64
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
第一种与第二种建表方式统计信息部分、等待事件部分都几乎完全一样，特别需要注意两种建表方式都有一个'另我兴奋'的等待事件：direct path write，这个等待事件说明ORACLE直接从PGA写数据到数据文件或临时文件绕过了SGA。不过另我不惑的是第二种建表方式的跟踪文件里没有找到类似insert /*+ append */的递归语句。可能本来就不该有？
还可以通过autotrace辅助证明，create table ...as select ..。采取的是直接路径加载。
SQL> alter system flush buffer_cache;
系统已更改。
已用时间: 00: 00: 00.20
SQL> set autotrace on statistics
SQL> create table s as select * from dba_objects;
表已创建。
已用时间: 00: 00: 02.62
SQL> select count(*) from s;
COUNT(*)
----------
49900
已用时间: 00: 00: 00.57
统计信息
------------------------------------------------------
28 recursive calls
0 db block gets
762 consistent gets
686 physical reads
0 redo size
409 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
统计资料显示全是物理读取，说明ORACLE绕过了SGA，直接通过PGA来写数据文件了。
实验结论如下：
create table as select ..建表语句采用直接路径加载的方式进行，效果等同于先create table再采用直接路径insert。
[ 本帖最后由 wei-xh 于 2010-6-27 14:53 编辑 ]
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/22034023/viewspace-666440/ ，如需转载，请注明出处，否则将追究法律责任。
