Oracle Append Hints

 APPEND HINTS 总结：

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

APPEND的优势：

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

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

缺点：

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

--以下是是测试: 

1. --ORACLE版本
   
2. SQL> SELECT * FROM V$VERSION;
   
3. 
   
4. BANNER
   
5. ----------------------------------------------------------------
   
6. Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
   
7. PL/SQL Release 10.2.0.1.0 - Production
   
8. CORE 10.2.0.1.0 Production
   
9. TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
   
10. NLSRTL Version 10.2.0.1.0 - Production
    
11. 
    
12. ----非归档模式,
    
13. SQL> SELECT NAME,LOG_MODE FROM V$DATABASE;
    
14. 
    
15. NAME LOG_MODE
    
16. --------- ------------
    
17. ORCL NOARCHIVELOG
    
18. 
    
19. --创建两个测试表
    
20. SQL> CREATE TABLE T1 TABLESPACE TEST AS SELECT * FROM DBA_OBJECTS WHERE 1=2;
    
21. 
    
22. 表已创建。
    
23. 
    
24. SQL> CREATE TABLE T2 TABLESPACE TEST AS SELECT * FROM DBA_OBJECTS WHERE 1=2;
    
25. 
    
26. 表已创建。
    
27. 
    
28. --记录当前的sesison的redo size
    
29. 
    
30. SQL> SELECT A.SID,B.NAME,A.VALUE FROM V$MYSTAT A, V$STATNAME B WHERE A.STATISTIC# = B.STATISTIC#
    
31.   2 AND B.STATISTIC# = 134;
    
32. 
    
33.        SID NAME VALUE
    
34. ---------- ----------------------- ----------
    
35.        144 redo size 89016
    
36. ----普通insert
    
37. SQL> INSERT INTO T1 SELECT * FROM DBA_OBJECTS;
    
38. 
    
39. 已创建52019行。
    
40. 
    
41. SQL> COMMIT;
    
42. 
    
43. 提交完成。
    
44. ----查看产生的redo size。(减去上面的redo size 89016,值为 5928472)
    
45. SQL> SELECT A.SID,B.NAME,A.VALUE FROM V$MYSTAT A, V$STATNAME B WHERE A.STATISTIC# = B.STATISTIC#
    
46.   2 AND B.STATISTIC# = 134;
    
47. 
    
48.        SID NAME VALUE
    
49. ---------- ----------------------- ----------
    
50.        144 redo size 6017488
    
51.        
    
52. ---append模式的 insert语句
    
53. 
    
54. SQL> INSERT /*+ APPEND */INTO T2 SELECT * FROM DBA_OBJECTS;
    
55. 
    
56. 已创建52018行。
    
57. 
    
58. SQL> COMMIT;
    
59. 
    
60. 提交完成。
    
61. --查看产生的redo size。(减去上面的redo size 6017488,值为 63584)
    
62. 
    
63. SQL> SELECT A.SID,B.NAME,A.VALUE FROM V$MYSTAT A, V$STATNAME B WHERE A.STATISTIC# = B.STATISTIC#
    
64.   2 AND B.STATISTIC# = 134;
    
65. 
    
66.        SID NAME VALUE
    
67. ---------- --------------------------- ----------
    
68.        144 redo size 6081072
    
69.        
    
70. ---修改数据库为归档模式，测试append的影响
    
71. SQL> SHUTDOWN IMMEDIATE
    
72. SQL> STARTUP MOUNT
    
73. ORACLE 例程已经启动。
    
74. SQL> ALTER DATABASE ARCHIVELOG;
    
75. SQL> ALTER DATABASE OPEN;
    
76. SQL> SELECT NAME,LOG_MODE FROM V$DATABASE;
    
77. 
    
78. NAME LOG_MODE
    
79. --------- ------------
    
80. ORCL ARCHIVELOG
    
81. ----截断表
    
82. SQL> TRUNCATE TABLE T1;
    
83. 
    
84. 表被截断。
    
85. 
    
86. SQL> TRUNCATE TABLE T2;
    
87. 
    
88. 表被截断。
    
89. ---查看当前redo size
    
90. SQL> SELECT A.SID,B.NAME,A.VALUE FROM V$MYSTAT A, V$STATNAME B WHERE A.STATISTIC# = B.STATISTIC#
    
91.   2 AND B.STATISTIC# = 134;
    
92. 
    
93.        SID NAME VALUE
    
94. ---------- --------------------------- ----------
    
95.        159 redo size 0
    
96. 
    
97. --普通insert 产生的redo size 5830728
    
98. SQL> INSERT INTO T1 SELECT * FROM DBA_OBJECTS;
    
99. 
    
100. 已创建52018行。
     
101. 
     
102. SQL> COMMIT;
     
103. 
     
104. 提交完成。
     
105. 
     
106. SQL> SELECT A.SID,B.NAME,A.VALUE FROM V$MYSTAT A, V$STATNAME B WHERE A.STATISTIC# = B.STATISTIC#
     
107.   2 AND B.STATISTIC# = 134;
     
108. 
     
109.        SID NAME VALUE
     
110. ---------- ------------------------- ----------
     
111.        159 redo size 5830728
     
112. --修改表t2为nologging的属性，记录当前redo size
     
113. SQL> alter table t2 nologging;
     
114. 
     
115. 表已更改。
     
116. 
     
117. SQL> SELECT A.SID,B.NAME,A.VALUE FROM V$MYSTAT A, V$STATNAME B WHERE A.STATISTIC# = B.STATISTIC#
     
118.   2 AND B.STATISTIC# = 134;
     
119. 
     
120.        SID NAME VALUE
     
121. ---------- --------------- ----------
     
122.        159 redo size 11796512
     
123. --APPEND 模式 insert 产生的redo size 为63616(11860128-11796512)
     
124. SQL> INSERT /*+ APPEND */INTO T2 SELECT * FROM DBA_OBJECTS;
     
125. 
     
126. 已创建52018行。
     
127. 
     
128. SQL> commit;
     
129. 
     
130. 提交完成。
     
131. 
     
132. SQL> SELECT A.SID,B.NAME,A.VALUE FROM V$MYSTAT A, V$STATNAME B WHERE A.STATISTIC# = B.STATISTIC#
     
133.   2 AND B.STATISTIC# = 134;
     
134. 
     
135.        SID NAME VALUE
     
136. ---------- --------------- ----------
     
137.        159 redo size 11860128
     
138. --修改表 t2 logging的模式
     
139. SQL> ALTER TABLE T2 LOGGING;
     
140. 
     
141. 表已更改。

另外对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.