查看数据库模式：
SQL> connect /as sysdba
Connected.
SQL> select name,log_mode from v$database;
NAME LOG_MODE
------------------ ------------------------
ORCL10G NOARCHIVELOG
可知：非归档模式
确认表的大小
SQL> connect sjzyk/sjzyk
Connected.
SQL> select t.segment_name,t.BYTES/1024/1024||'mb' from user_segments t ;
SEGMENT_NAME
--------------------------------------------------------------------------------
T.BYTES/1024/1024||'MB'
--------------------------------------------------------------------------------
SJ_TEST1
256mb
SQL> set autotrace traceonly statistics;
SQL> insert into sj_test2 as select * from sj_test1;
insert into sj_test2 as select * from sj_test1
*
ERROR at line 1:
ORA-00926: missing VALUES keyword
SQL> insert into sj_test2 select * from sj_test1;
1000000 rows created.
Statistics
----------------------------------------------------------
5291 recursive calls
283525 db block gets
119170 consistent gets
32033 physical reads
262035336 redo size
665 bytes sent via SQL*Net to client
579 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
1000000 rows processed
从上面记录中，insert普通语句产生redo大小约为260M左右
SQL> set autotrace traceonly statistics;
SQL> insert /*+ APPEND */ into sj_test2 select * from sj_test1;
1000000 rows created.
Statistics
----------------------------------------------------------
5093 recursive calls
36678 db block gets
34225 consistent gets
31611 physical reads
371512 redo size
663 bytes sent via SQL*Net to client
593 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1000000 rows processed
SQL> commit;
添加/*+ APPEND */直插后redo产生日志大约为300kb
SQL> set autotrace traceonly statistics;
SQL> insert /*+ APPEND */ into sj_test2 nologging select * from sj_test1;
1000000 rows created.
Statistics
----------------------------------------------------------
5222 recursive calls
36678 db block gets
34238 consistent gets
20265 physical reads
372068 redo size
663 bytes sent via SQL*Net to client
606 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1000000 rows processed
采用/*+ APPEND */和nologging的日志与直插区别不是很大，
SQL> set autotrace traceonly statistics;
SQL> insert into sj_test2 nologging select * from sj_test1;
1000000 rows created.
Statistics
----------------------------------------------------------
5098 recursive calls
283651 db block gets
85806 consistent gets
20922 physical reads
260028080 redo size
680 bytes sent via SQL*Net to client
590 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1000000 rows processed
采用nologging模式下redo日志差不多也是260m，至少在非归档模式下，nologging的左右不是很大
在SYS用户下创建视图和公共映射
create or replace view redo_size as
select value
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';
CREATE PUBLIC synonym REDO_SIZE FOR REDO_SIZE;
SQL> select * from redo_size;
VALUE
----------
3154424
SQL> create table sj_test2 as select * from sj_test1;
Table created.
SQL> select * from redo_size;
VALUE
----------
3568676
对比CREATE TABLE 创建前后值减去之前的redo值500kb左右
初步测试：create table 语句对于抽取历史数据（大批量数据）产生的redo日志较少，插入数据较快。网上百度说是CREATE TABLE 为数据定义语言，产生少量的redo,undo。
应用资料：http://www.2cto.com/database/201303/193306.html
