Oracle Append Hints

 APPEND HINTS 总结：

 APPEND 属于direct insert。归档模式下append+table nologging会大量减少日志, 非归档模式append会大量减少日志,append方式插入只会产生很少的undo。

APPEND的优势：

一是减少对空间的搜索，直接在高水位之上插入。

二是NOLOGGING会大大减少redo log的产生。

缺点：

浪费空间，使表的空间占用量大大增加。除非表只增加不删除。
普通INSERT 默认会从delete掉的空块中插入数据。

 

 

--以下是是测试:

 

--ORACLE版本
SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

----非归档模式,
SQL> SELECT NAME,LOG_MODE FROM V$DATABASE;

NAME      LOG_MODE
--------- ------------
ORCL      NOARCHIVELOG

--创建两个测试表
SQL> CREATE TABLE T1 TABLESPACE TEST AS SELECT * FROM DBA_OBJECTS WHERE 1=2;

表已创建。

SQL> CREATE TABLE T2 TABLESPACE TEST AS SELECT * FROM DBA_OBJECTS WHERE 1=2;

表已创建。

--记录当前的sesison的redo size

SQL> SELECT A.SID,B.NAME,A.VALUE  FROM V$MYSTAT A, V$STATNAME B WHERE A.STATISTIC# = B.STATISTIC#
  2  AND B.STATISTIC# = 134;

       SID NAME                      VALUE
---------- ----------------------- ----------
       144 redo size                 89016
----普通insert
SQL> INSERT INTO T1 SELECT * FROM DBA_OBJECTS;

已创建52019行。

SQL> COMMIT;

提交完成。
----查看产生的redo size。(减去上面的redo size 89016,值为 5928472)
SQL> SELECT A.SID,B.NAME,A.VALUE  FROM V$MYSTAT A, V$STATNAME B WHERE A.STATISTIC# = B.STATISTIC#
  2  AND B.STATISTIC# = 134;

       SID NAME                     VALUE
---------- ----------------------- ----------
       144 redo size                6017488
       
---append模式的 insert语句

SQL> INSERT /*+ APPEND */INTO T2 SELECT * FROM DBA_OBJECTS;

已创建52018行。

SQL> COMMIT;

提交完成。
--查看产生的redo size。(减去上面的redo size 6017488,值为 63584)

SQL> SELECT A.SID,B.NAME,A.VALUE  FROM V$MYSTAT A, V$STATNAME B WHERE A.STATISTIC# = B.STATISTIC#
  2  AND B.STATISTIC# = 134;

       SID NAME                        VALUE
---------- --------------------------- ----------
       144 redo size                   6081072
       
---修改数据库为归档模式，测试append的影响    
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
ORACLE 例程已经启动。
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
SQL> SELECT NAME,LOG_MODE FROM V$DATABASE;

NAME      LOG_MODE
--------- ------------
ORCL      ARCHIVELOG
----截断表
SQL> TRUNCATE TABLE T1;

表被截断。

SQL> TRUNCATE TABLE T2;

表被截断。
---查看当前redo size
SQL> SELECT A.SID,B.NAME,A.VALUE  FROM V$MYSTAT A, V$STATNAME B WHERE A.STATISTIC# = B.STATISTIC#
  2  AND B.STATISTIC# = 134;

       SID NAME                        VALUE
---------- --------------------------- ----------
       159 redo size                    0

--普通insert 产生的redo size 5830728
SQL> INSERT INTO T1 SELECT * FROM DBA_OBJECTS;

已创建52018行。

SQL> COMMIT;

提交完成。

SQL> SELECT A.SID,B.NAME,A.VALUE  FROM V$MYSTAT A, V$STATNAME B WHERE A.STATISTIC# = B.STATISTIC#
  2  AND B.STATISTIC# = 134;

       SID NAME                      VALUE
---------- ------------------------- ----------
       159 redo size                 5830728
--修改表t2为nologging的属性，记录当前redo size
SQL> alter table t2 nologging;

表已更改。

SQL> SELECT A.SID,B.NAME,A.VALUE  FROM V$MYSTAT A, V$STATNAME B WHERE A.STATISTIC# = B.STATISTIC#
  2  AND B.STATISTIC# = 134;

       SID NAME                 VALUE
---------- --------------- ----------
       159 redo size         11796512
--APPEND 模式 insert 产生的redo size 为63616(11860128-11796512)
SQL> INSERT /*+ APPEND */INTO T2 SELECT * FROM DBA_OBJECTS;

已创建52018行。

SQL> commit;

提交完成。

SQL> SELECT A.SID,B.NAME,A.VALUE  FROM V$MYSTAT A, V$STATNAME B WHERE A.STATISTIC# = B.STATISTIC#
  2  AND B.STATISTIC# = 134;

       SID NAME                 VALUE
---------- --------------- ----------
       159 redo size         11860128
--修改表 t2 logging的模式
SQL> ALTER TABLE T2 LOGGING;

表已更改。

 

另外对nologging的一点使用记录：

DML statements (UPDATE, DELETE, and conventional path insert) are unaffected by the NOLOGGING attribute of the table and generate redo.

The logging_clause lets you specify whether creation of a database object will be logged in the redo log file (LOGGING) or not (NOLOGGING).

 

You can specify the logging_clause in the following statements:

CREATE TABLE and ALTER TABLE

CREATE INDEX and ALTER INDEX

CREATE MATERIALIZED VIEW and ALTER MATERIALIZED VIEW

CREATE MATERIALIZED VIEW LOG and ALTER MATERIALIZED VIEW LOG

CREATE TABLESPACE and ALTER TABLESPACE

SQL*Loader and direct load INSERT operations

This has the following benefits: 

Space is saved in the redo log files.

The time it takes to create the table is decreased.

Performance improves for parallel creation of large tables.