①创建表：
SQL> create table t4 as select * from all_objects; Table created.
②设置t4处于nologging：
SQL> alter table t4 nologging; Table altered.
③开启autotrace查看执行计划：
SQL> set autotrace on
④插入数据：
SQL> insert into t4 select * from t4; 85184 rows created. Execution Plan
----------------------------------------------------------
Plan hash value: 2560505625 --------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
| --------------------------------------------------------------------------------
- | 0 | INSERT STATEMENT | | 172K| 25M| 661 (1)| 00:00:08
| | 1 | LOAD TABLE CONVENTIONAL | T4 | | | |
| | 2 | TABLE ACCESS FULL | T4 | 172K| 25M| 661 (1)| 00:00:08
| --------------------------------------------------------------------------------
- Note
-----
- dynamic sampling used for this statement (level=2) Statistics
----------------------------------------------------------
162 recursive calls
6834 db block gets
3798 consistent gets
1215 physical reads
9943036 redo size
838 bytes sent via SQL*Net to client
788 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
85184 rows processed
⑤使用append插入数据：
SQL> insert /*+ append */ into t4 select * from t4; 170368 rows created. Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel SP2-0612: Error generating AUTOTRACE EXPLAIN report Statistics
----------------------------------------------------------
49 recursive calls
2687 db block gets
2529 consistent gets
0 physical reads
28156 redo size
823 bytes sent via SQL*Net to client
802 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
170368 rows processed
比较一下普通插入和append插入生成的redo大小，明显append插入时生成的redo小很多。
SQL> insert /*+ append */ into t4 select * from t4;
170368 rows created.
Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel
SP2-0612: Error generating AUTOTRACE EXPLAIN report
Statistics
----------------------------------------------------------
49 recursive calls
2687 db block gets
2529 consistent gets
0 physical reads
28156 redo size
823 bytes sent via SQL*Net to client
802 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
170368 rows processed