【测试】自行建表并演示append+nologging，并描述数据写入后产生的效果

①创建表：

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