Append Hint
轉: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.
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:
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/195110/viewspace-774814/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [原創]append_values hintAPP
- 關於append sql hint的實驗APPSQL
- append HINT 的對事務的影響APP
- 11gr2,新增hint APPEND_VALUESAPP
- [Oracle] Append hint(insert sql) will lead a TM LOCKOracleAPPSQL
- APPEND_VALUES Hint in Oracle Database 11g Release 2APPOracleDatabase
- 【sql調優之執行計劃】使用hint(六) append and noappendSQLAPP
- 通過append hint來插入資料,演示它和普通插入資料的效能比較。APP
- 關於加快INSERT語句執行速度和 HINT /*+ append*/及nologging的使用APP
- URLSearchParams append()APP
- jQuery append()jQueryAPP
- golang appendGolangAPP
- oracle hintOracle
- append2 給append 新增回撥方法APP
- oracle hint之hint_index_ffs,index_joinOracleIndex
- python append()PythonAPP
- URLSearchParams append() 方法APP
- ORACLE 部分HINTOracle
- oracle hint (續)Oracle
- Oracle Hint 精華Oracle
- 【sql hint 提示】SQL
- sql hint articleSQL
- Oralce SQL hintSQL
- 【TUNE_ORACLE】Oracle Hint之常用Hint功能概述(五)Oracle
- 【TUNE_ORACLE】Oracle Hint之常用Hint功能概述(四)Oracle
- 【TUNE_ORACLE】Oracle Hint之常用Hint功能概述(三)Oracle
- 【TUNE_ORACLE】Oracle Hint之常用Hint功能概述(二)Oracle
- 【TUNE_ORACLE】Oracle Hint之常用Hint功能概述(一)Oracle
- 【HINT】使用“NO_INDEX ”Hint提示避免SQL使用特定索引IndexSQL索引
- zt_Oracle hint driving_site Hint的用法Oracle
- LOCAL INDEX和HINT的使用【DO BE USED LOCAL INDEX IN HINT】薦Index
- oracle之hint概述Oracle
- oracle常見hintOracle
- mysql常用的hintMySql
- MySQL SQL hint 提示MySql
- oracle hint簡述Oracle
- oracle hint (續-0)Oracle
- oracle hint_no_indexOracleIndex