Oracle DML NOLOGGING

一．說明

在對大表插入資料的時候，經常會用到nologging 選項。Nologging 并不是不産生redo，nologging + direct 隻是不會對資料産生 redo（但依然有其他的redo）。

同理 logging + direct 下 undo 也是大大地減少，減少的是資料的undo ，這裡強調的是undoredo的減少也一樣，是資料本身的 redoredo 和 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，沒什麼變化

将表改為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的DMLnologging 和 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)

On Archive Mode, TABLEs must be nologging... and 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 ...

Oracle DML NOLOGGING

繼續閱讀

【Torch】最簡潔logging使用指南

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述

Oracle 批量查詢傳入List 傳回List