轉：http://www.oracle-base.com/articles/misc/append-hint.php#how-the-append-affects-redo-generation

APPEND Hint

• How the APPEND Hint Affects Performance
• How the APPEND Hint Affects the Table Size (High Water Mark)
• How the APPEND Hint Affects Redo Generation

Related articles.

• APPEND_VALUES Hint in Oracle Database 11g Release 2

How the APPEND Hint Affects Performance

The APPEND hint tells the optimizer to perform. a direct-path insert, which improves the performance of INSERT .. SELECT operations for a number of reasons:

• Data is appended to the end of the table, rather attempting to use existing free space within the table.
• Data is written directly to the data files, by passing the buffer cache.
• Referential integrity constraints are ignored.

The combination of these features make direct-path inserts significantly quicker than conventional-path inserts.

How the APPEND Hint Affects the Table Size (High Water Mark)

As direct-path inserts append data to the end of the table, they constantly increase the table high water mark, even if there is lots of free space within the table. In tables that regularly have rows deleted, the use of the the APPEND hint can result in large tables containing lots of sparsely populated blocks. These will need to be managed by one of the following types of shrink operation.

• Export the data, truncate the table and import the data.
• Use a "CREATE TABLE ... AS SELECT" (CTAS) operation to build a new table with the data compacted, drop the original table and rename the new table to replace the original.
• Use and online table redefinition operation to recreate the table.
• Use an online segment shrink operation to compact the data.

How the APPEND Hint Affects Redo Generation

If the database is running on NOARCHIVELOG mode, using just the APPEND hint will reduce redo generation. In reality, you will rarely run OLTP databases in NOARCHIVELOG mode, so what happens in ARCHIVELOG mode? In ARCHIVELOG mode, using the APPEND hint will not reduce redo generation unless the table is set to NOLOGGING. The examples below step through this process to show it in action.

The following example is run against a database running in NOARCHIVELOG mode. The redo generation is displayed in bold.

SQL> CREATE TABLE t1 AS SELECT * FROM all_objects WHERE 1=2;

Table created.

SQL> SET AUTOTRACE ON STATISTICS
SQL> INSERT INTO t1 SELECT * FROM all_objects;

72512 rows created.


Statistics
----------------------------------------------------------
        634  recursive calls
       9946  db block gets
      50116  consistent gets
          2  physical reads
    8464520  redo size
        830  bytes sent via SQL*Net to client
        796  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
       1508  sorts (memory)
          0  sorts (disk)
      72512  rows processed

SQL> TRUNCATE TABLE t1;

Table truncated.

SQL> INSERT /*+ APPEND */ INTO t1 SELECT * FROM all_objects;

72512 rows created.


Statistics
----------------------------------------------------------
        369  recursive calls
       1689  db block gets
      48194  consistent gets
          2  physical reads
      46048  redo size
        822  bytes sent via SQL*Net to client
        810  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
       1500  sorts (memory)
          0  sorts (disk)
      72512  rows processed

SQL> COMMIT;

Commit complete.

SQL>

As suggested, with the database running on ARCHIVELOG mode, the addition of the APPEND hint did reduce the amount of redo generated.

The next example performs the same test, but this time on a database running in ARCHIVELOG mode.

SQL> CREATE TABLE t1 AS SELECT * FROM all_objects WHERE 1=2;

Table created.

SQL> SET AUTOTRACE ON STATISTICS
SQL> INSERT INTO t1 SELECT * FROM all_objects;

88773 rows created.


Statistics
----------------------------------------------------------
        613  recursive calls
      11792  db block gets
     116808  consistent gets
          2  physical reads
   10222352  redo size
        370  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
       3142  sorts (memory)
          0  sorts (disk)
      88773  rows processed

SQL> TRUNCATE TABLE t1;

Table truncated.

SQL> INSERT /*+ APPEND */ INTO t1 SELECT * FROM all_objects;

88773 rows created.


Statistics
----------------------------------------------------------
        307  recursive calls
       1573  db block gets
     114486  consistent gets
          0  physical reads
   10222864  redo size
        366  bytes sent via SQL*Net to client
        566  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
       3138  sorts (memory)
          0  sorts (disk)
      88773  rows processed

SQL> COMMIT;

Commit complete.

SQL>

Notice how the addition of the APPEND hint no longer has an impact on the amount of redo generated.

To allow the APPEND hint to have an impact on redo generation again, we must set the table to NOLOGGING.

SQL> ALTER TABLE t1 NOLOGGING;

Table altered.

SQL> TRUNCATE TABLE t1;

Table truncated.

SQL> SET AUTOTRACE ON STATISTICS
SQL> INSERT INTO t1 SELECT * FROM all_objects;

88773 rows created.


Statistics
----------------------------------------------------------
        506  recursive calls
      11790  db block gets
     116652  consistent gets
          0  physical reads
   10222328  redo size
        373  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
       3139  sorts (memory)
          0  sorts (disk)
      88773  rows processed

SQL> TRUNCATE TABLE t1;

Table truncated.

SQL> INSERT /*+ APPEND */ INTO t1 SELECT * FROM all_objects;

88773 rows created.


Statistics
----------------------------------------------------------
        307  recursive calls
       1573  db block gets
     114486  consistent gets
          0  physical reads
      25968  redo size
        366  bytes sent via SQL*Net to client
        566  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
       3138  sorts (memory)
          0  sorts (disk)
      88773  rows processed

SQL> COMMIT;

Commit complete.

SQL> DROP TABLE t1 PURGE;

Table dropped.

SQL>

We can see that altering the table to NOLOGGING makes the behavior. return.

So, except in the case of a NOARCHIVELOG mode database, the APPEND hint will only reduce redo generation if the table[space] is set to NOLOGGING.

For more information see:

• APPEND Hint
• APPEND_VALUES Hint in Oracle Database 11g Release 2