Oracle DML NOLOGGING
一． NOLOGGING 說明
在對大表插入資料的時候，經常會用到nologging 選項。Nologging 並不是不產生redo，nologging + direct 只是不會對資料產生 redo（但依然有其他的redo）。
同理 logging + direct 下 undo 也是大大地減少， 減少的是 資料的undo ，這裡強調的是 資料本身的undo ，就如同 redo的減少也一樣，是 資料本身的 redo ，這和資料庫是否產生 redo 和 undo 是不同的概念，比如空間分配的 redo and undo ,這就不是資料本身的變化。
在非歸檔模式下, 對於nologging 和 logging模式，只有使用 append，才不會對資料生成redo。
在歸檔模式下,只有將表置於nologging 模式，並且使用append 才不會對資料生成redo.
二． 歸檔模式下的示例
兩個查詢用的指令碼
--new.sql
column OLD_VALUE new_value OLD_VALUE
select value OLD_VALUE
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';
--diff.sql
select (value - &OLD_VALUE) OLD_VALUE
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';
資料庫執行在歸檔模式
SQL> archive log list
資料庫日誌模式 存檔模式
自動存檔 啟用
存檔終點 d:/archivelog
最早的聯機日誌序列 125
下一個存檔日誌序列 127
當前日誌序列 127
2.1 Create TABLE
SQL> @?/new.sql
OLD_VALUE
----------
8535492
SQL> create table T_NOLOG nologging as select * from all_objects;
表已建立。
SQL> @?/diff
原值 1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where
新值 1: select (value - 8535492) OLD_VALUE from v$mystat, v$statname where
OLD_VALUE
----------
83496
注：REDO SIZE=83496
SQL> @?/new.sql
OLD_VALUE
----------
8618988
SQL> create table T_LOG logging as select * from all_objects;
表已建立。
SQL> @?/diff
原值 1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where
新值 1: select (value - 8618988) OLD_VALUE from v$mystat, v$statname where
OLD_VALUE
----------
8491836
注：REDO SIZE=8491836
總結：通過上面的2個例子，可以看出用nologging 建立表，不會對資料生成redo，僅對資料字典生成redo.
create table with nologging... not generate redo, just generate for data dictionary
2.2 DELETE
SQL> @?/new.sql
OLD_VALUE
----------
17110824
SQL> DELETE FROM T_NOLOG;
已刪除71711行。
SQL> @?/diff
原值 1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where
新值 1: select (value - 17110824) OLD_VALUE from v$mystat, v$statname where
OLD_VALUE
----------
26592364
注：REDO SIZE=26592364
SQL> @?/new.sql
OLD_VALUE
----------
43703188
SQL> DELETE FROM T_LOG;
已刪除71712行。
SQL> @?/diff
原值 1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where
新值 1: select (value - 43703188) OLD_VALUE from v$mystat, v$statname where
OLD_VALUE
----------
26592560
注：REDO SIZE=26592560
2.3 INSERT
SQL> @?/new.sql
OLD_VALUE
----------
70295748
SQL> INSERT INTO T_NOLOG SELECT * FROM ALL_OBJECTS;
已建立71712行。
SQL> @?/diff
原值 1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where
新值 1: select (value - 70295748) OLD_VALUE from v$mystat, v$statname where
OLD_VALUE
----------
8169900
注：REDO SIZE=8169900
SQL> @?/new.sql
OLD_VALUE
----------
78465648
SQL> INSERT INTO T_LOG SELECT * FROM ALL_OBJECTS;
已建立71712行。
SQL> @?/diff
原值 1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where
新值 1: select (value - 78465648) OLD_VALUE from v$mystat, v$statname where
OLD_VALUE
----------
8169796
注：REDO SIZE=8169796
2.4 UPDATE
SQL> @?/new.sql
OLD_VALUE
----------
86635444
SQL> UPDATE T_NOLOG SET OBJECT_ID=1;
已更新71712行。
SQL> @?/diff
原值 1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where
新值 1: select (value - 86635444) OLD_VALUE from v$mystat, v$statname where
OLD_VALUE
----------
24323896
注：REDO SIZE=24323896
SQL> @?/new.sql
OLD_VALUE
----------
110959340
SQL> UPDATE T_LOG SET OBJECT_ID=1;
已更新71712行。
SQL> @?/diff
原值 1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where
新值 1: select (value - 110959340) OLD_VALUE from v$mystat, v$statname where
OLD_VALUE
----------
36490988
注：REDO SIZE=20911424
總結： insert/update/delete 的DML 操作，在logging和nologging上沒有區別
On DML insert/update/delete redo size with nologging not difference... with logging.
2.5 Show case "APPEND" hints
2.5.1 table NOLOGGING and not use APPEND hints
SQL> @?/new.sql
OLD_VALUE
----------
147450328
SQL> INSERT INTO T_NOLOG SELECT * FROM ALL_OBJECTS;
已建立71712行。
SQL> @?/diff
原值 1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where
新值 1: select (value - 147450328) OLD_VALUE from v$mystat, v$statname where
OLD_VALUE
----------
8325816
注： REDO SIZE=8325816
SQL> @?/new.sql
OLD_VALUE
----------
155776144
SQL> INSERT /*+ APPEND */ INTO T_NOLOG SELECT * FROM ALL_OBJECTS;
已建立71712行。
SQL> @?/diff
原值 1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where
新值 1: select (value - 155776144) OLD_VALUE from v$mystat, v$statname where
OLD_VALUE
----------
14852
注：REDO SIZE=14852，採用append後，redo size大幅減小
2.5.2 table LOGGING, and use APPEND hints
SQL> @?/new.sql
OLD_VALUE
----------
155790996
SQL> INSERT /*+ APPEND */ INTO T_LOG SELECT * FROM ALL_OBJECTS;
已建立71712行。
SQL> @?/diff
原值 1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where
新值 1: select (value - 155790996) OLD_VALUE from v$mystat, v$statname where
OLD_VALUE
----------
8406068
注：REDO SIZE=8640396， redo size 沒什麼變化
將表改為nologging 模式，在檢視
SQL> @?/new.sql
OLD_VALUE
----------
164200200
SQL> INSERT /*+ APPEND */ INTO T_LOG SELECT * FROM ALL_OBJECTS;
已建立71712行。
SQL> @?/diff
原值 1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where
新值 1: select (value - 164200200) OLD_VALUE from v$mystat, v$statname where
OLD_VALUE
----------
15012
注：REDO SIZE=27956
總結： 通過實驗看出，
對於logging 模式， 使用append hint 在生成redo 上沒有什麼變化
對於nologging模式，使用append hint 對資料沒有生成redo，僅對資料字典生成了redo.
APPEND hints on table "logging" not difference (generate redo).
If "alter table nologging" before, and then insert (append)... it's work with nologging (not generate redo, just redo for data dictionary).
三． 非歸檔模式下的示例
SQL> archive log list
資料庫日誌模式 非存檔模式
自動存檔 禁用
存檔終點 d:/archivelog
最早的聯機日誌序列 129
當前日誌序列 131
3.1 Create TABLE
SQL> @?/new.sql
OLD_VALUE
----------
113788
SQL> create table T_NOLOG nologging as select * from all_objects;
表已建立。
SQL> @?/diff
原值 1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where
新值 1: select (value - 113788) OLD_VALUE from v$mystat, v$statname where
OLD_VALUE
----------
87244
注：redo size=87244
SQL> @?/new.sql
OLD_VALUE
----------
201032
SQL> create table T_LOG logging as select * from all_objects;
表已建立。
SQL> @?/diff
原值 1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where
新值 1: select (value - 201032) OLD_VALUE from v$mystat, v$statname where
OLD_VALUE
----------
81324
注： redo size=81324
總結： 在非歸檔模式下，create table 在nologging 和 logging 模式差別不大。
When create table (noarchive mode) LOGGING table not different NOLOGGING table
3.2 DML -- DELETE
SQL> @?/new.sql
OLD_VALUE
----------
282356
SQL> DELETE FROM T_NOLOG;
已刪除71711行。
SQL> @?/diff
原值 1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where
新值 1: select (value - 282356) OLD_VALUE from v$mystat, v$statname where
OLD_VALUE
----------
26591628
注： redo size=26591628
SQL> @?/new.sql
OLD_VALUE
----------
26873984
SQL> DELETE FROM T_LOG;
已刪除71712行。
SQL> @?/diff
原值 1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where
新值 1: select (value - 26873984) OLD_VALUE from v$mystat, v$statname where
OLD_VALUE
----------
26590272
注： redo size= 26590272
3.3 DML -- INSERT
SQL> @?/new.sql
OLD_VALUE
----------
53464256
SQL> INSERT INTO T_NOLOG SELECT * FROM ALL_OBJECTS;
已建立71712行。
SQL> @?/diff
原值 1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where
新值 1: select (value - 53464256) OLD_VALUE from v$mystat, v$statname where
OLD_VALUE
----------
8170184
注：redo size = 8170184
SQL> @?/new.sql
OLD_VALUE
----------
61634440
SQL> INSERT INTO T_LOG SELECT * FROM ALL_OBJECTS;
已建立71712行。
SQL> @?/diff
原值 1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where
新值 1: select (value - 61634440) OLD_VALUE from v$mystat, v$statname where
OLD_VALUE
----------
8169840
注： redo size= 8169840
3.4 DML -- UPDATE
SQL> @?/new.sql
OLD_VALUE
----------
69804280
SQL> UPDATE T_NOLOG SET OBJECT_ID=1;
已更新71712行。
SQL> @?/diff
原值 1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where
新值 1: select (value - 69804280) OLD_VALUE from v$mystat, v$statname where
OLD_VALUE
----------
20164888
注： redo size = 20164888
SQL> @?/new.sql
OLD_VALUE
----------
89969168
SQL> UPDATE T_LOG SET OBJECT_ID=1;
已更新71712行。
SQL> @?/diff
原值 1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where
新值 1: select (value - 89969168) OLD_VALUE from v$mystat, v$statname where
OLD_VALUE
----------
21674776
注： redo size=21674776
總結： 對於 INSERT/UPDATE/DELETE的DML 操作， nologging 和 logging 模式沒有什麼區別
On DML INSERT/UPDATE/DELETE not different between NOLOGGING and LOGGING
3.5 INSERT /*+ APPEND */
SQL> @?/new.sql
OLD_VALUE
----------
111643944
SQL> INSERT INTO T_NOLOG SELECT * FROM ALL_OBJECTS;
已建立71712行。
SQL> @?/diff
原值 1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where
新值 1: select (value - 111643944) OLD_VALUE from v$mystat, v$statname where
OLD_VALUE
----------
8325816
注： redo size=8325816
SQL> @?/new.sql
OLD_VALUE
----------
119969760
SQL> INSERT /*+ APPEND */ INTO T_NOLOG SELECT * FROM ALL_OBJECTS;
已建立71712行。
SQL> @?/diff
原值 1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where
新值 1: select (value - 119969760) OLD_VALUE from v$mystat, v$statname where
OLD_VALUE
----------
14896
注：redo size=14896， 減小很多
SQL> @?/new.sql
OLD_VALUE
----------
119984656
SQL> INSERT INTO T_LOG SELECT * FROM ALL_OBJECTS;
已建立71712行。
SQL> @?/diff
原值 1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where
新值 1: select (value - 119984656) OLD_VALUE from v$mystat, v$statname where
OLD_VALUE
----------
8325832
注： redo size= 8325832
SQL> @?/new.sql
OLD_VALUE
----------
128310488
SQL> INSERT /*+ APPEND */ INTO T_LOG SELECT * FROM ALL_OBJECTS;
已建立71712行。
SQL> @?/diff
原值 1: select (value - &OLD_VALUE) OLD_VALUE from v$mystat, v$statname where
新值 1: select (value - 128310488) OLD_VALUE from v$mystat, v$statname where
OLD_VALUE
----------
14880
注：redo size=14880
總結： 對於非歸檔模式，對於nologging 和 logging模式，只有使用 append，才不會對資料生成redo。
對於歸檔模式，只有將表置於nologging 模式，並且使用append 才不會對資料生成redo.
一點注意的地方：
如果直接載入的表上有索引，Oracle不會像載入資料的方式那樣來處理索引的資料，但是它同樣需要維護一個索引，這個成本很高，同時會生成很多的redo。
所以當使用直接載入時，通常是針對一些資料量非常大的表。如果這些表存在索引，將會帶來很大的效能影響，這時可以考慮先將索引disable或者drop掉，等載入資料後，之後在重新建立索引。
On NoArchive Mode, Don't mind tables be nologging/logging... just use /*+ APPEND */ ,that will not generate redo (just data dictionary)
NOLOGGING: Oracle will generate a minimal number of redo log entries in order to protect the data dictionary, and the operation will probably run faster. Logging can be disabled at the table level or the tablespace level.
If it is done at the tablespace level then we create indexes or tables in this tablespace; they will be in NOLOGGING mode.
A table or an index can be created with NOLOGGING mode or it can be altered using ALTER TABLE/INDEX NOLOGGING.
NOLOGGING is active in the following situations and while running one of the following commands but not after that.
- DIRECT LOAD (SQL*Loader)
- DIRECT LOAD INSERT (using APPEND hint)
- CREATE TABLE ... AS SELECT
- CREATE INDEX
- ALTER TABLE MOVE
- ALTER TABLE ... MOVE PARTITION
- ALTER TABLE ... SPLIT PARTITION
- ALTER TABLE ... ADD PARTITION (if HASH partition)
- ALTER TABLE ... MERGE PARTITION
- ALTER TABLE ... MODIFY PARTITION, ADD SUBPARTITON, COALESCE SUBPARTITON, REBUILD UNUSABLE INDEXES
- ALTER INDEX ... SPLIT PARTITION
- ALTER INDEX ... REBUILD
- ALTER INDEX ... REBUILD PARTITION
Logging is stopped only while one of the commands above is running.
So if a user runs this: ALTER INDEX new_index NOLOGGING.
The actual rebuild of the index does not generate redo (all data dictionary changes associated with the rebuild will do) but after that any DML on the index will generate redo this includes direct load insert on the table which the index belongs to.
All the following statements will generate redo despite the fact the table is in NOLOGGING mode:
- INSERT INTO new_table_nolog_test ...,
- UPDATE new_table_nolog_test SET ...,
- DELETE FROM new_table_nolog_test ..
The following will not generate redo (except from dictionary changes and indexes):
- INSERT /*+APPEND+/ ...
- ALTER TABLE new_table_nolog_test MOVE ...
- ALTER TABLE new_table_nolog_test MOVE PARTITION ...
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22207394/viewspace-1207737/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle Parallel DMLOracleParallel
- 【DATAGUARD】Oracle Dataguard nologging 塊修復Oracle
- 原創：oracle DML介紹與使用Oracle
- oracle全文索引之commit與DML操作Oracle索引MIT
- Oracle 檢視可以DML操作的條件Oracle
- G008-ORACLE-DG ORACLE 19C Active Data Guard DML RedirectionOracle
- oracle點陣圖索引對DML操作的影響Oracle索引
- Oracle standby的ORA-01578 ORA-01110 ORA-26040 坑爹的NOLOGGINGOracle
- ORACLE 資料庫 查詢語句與DML語句Oracle資料庫
- Oracle 19c 新特性：ADG的自動DML重定向增強讀寫分離--ADG_REDIRECT_DMLOracle
- alter table nologging /*+APPEND PARALLEL(n)*/APPParallel
- Oracle 19C OGG基礎運維-03DML操作同步Oracle運維3D
- Oracle 19C Data Guard基礎運維-08 DML重定向Oracle運維
- nologging、force logging、supplemental log的理解
- ORACLE 分割槽索引UNUSABLE導致的DML操作失敗引起的血案Oracle索引
- Nologging對恢復的影響(二)
- Nologging對恢復的影響(一)
- oracle資料庫災難挽救應急方案之DML誤操作恢復Oracle資料庫
- Oracle 修復由於主庫NOLOGGING引起的備庫ORA-01578和ORA-26040錯誤Oracle
- DML_The OUTPUT Clause
- Oracle 19C OGG基礎運維-04DML同步常見問題Oracle運維
- 日誌記錄模式(LOGGING 、FORCE LOGGING 、NOLOGGING)模式
- 挽救DG中主庫的nologging操作的塊
- OGG 簡單DML同步
- Mysql DML 新增資料MySql
- MySQL DML運算元據MySql
- SQLServer DML操作阻塞SELECT查詢SQLServer
- 入門MySQL——DML語句篇MySql
- DDL、DML、DCL、DQL相關操作
- MySQL基礎之DML語句MySql
- Sysbench-0.5改成只有DML操作
- SQL學習___03：DML語法SQL
- 一次rman恢復引起的nologging問題模擬
- ORACLE DML執行計劃頻繁變更導致業務響應極慢問題的處理Oracle
- unusable index對DML/QUERY的影響Index
- MySQL全面瓦解5：資料操作-DMLMySql
- MySQL的DDL和DML操作語法MySql
- 【學習】SQL基礎-010-DMLSQL
- MySQL-DML（Data Manipulation Language）詳解MySql