Oracle 中的 logging/nologging 选项的目的是什么

What is the purpose of logging/nologging option in Oracle

提问by Sohel

What happens if I don't specify logging/nologging in database objects in Oracle? What I meant to say how would behave with logging/nologging in database objects and without logging/nologging in database objects?

如果我不在 Oracle 的数据库对象中指定 logging/nologging 会发生什么？我的意思是说，在数据库对象中使用日志记录/无日志记录以及在数据库对象中没有日志记录/无日志记录会如何表现？

回答by Jon Heller

LOGGING/NOLOGGING helps manage enabling direct path writes in order to reduce the generation of REDO and UNDO. It is one of several ways to control the delicate balance between recoverability and performance.

LOGGING/NOLOGGING 有助于管理启用直接路径写入，以减少 REDO 和 UNDO 的生成。它是控制可恢复性和性能之间微妙平衡的几种方法之一。

Oracle Architecture Background Information

Oracle 架构背景信息

REDOis how Oracle provides durability, the "D" in ACID. When a transaction is committed the changes are not necessarily stored neatly in the datafiles. That keeps things fast and lets background processes handle some work. REDO is a description of the change. It is stored quickly, on multiple disks, in a "dumb" log. Changes are fast and if the server loses power one microsecond after the commit returned, Oracle can go through the REDO logs to make sure that change isn't lost.

REDO是 Oracle 提供持久性的方式，即 ACID 中的“D”。提交事务时，更改不一定整齐地存储在数据文件中。这样可以保持快速并让后台进程处理一些工作。REDO 是对更改的描述。它被快速存储在多个磁盘上的“哑”日志中。更改很快，如果服务器在提交返回后一微秒断电，Oracle 可以查看 REDO 日志以确保更改不会丢失。

UNDOhelps Oracle provide consistency, the "C" in ACID. It stores a description of how to reverse the change. This information may be needed by another process that's reading the table and needs to know what the value usedto be at an older point-in-time.

UNDO帮助 Oracle 提供一致性，即 ACID 中的“C”。它存储了如何撤销更改的描述。另一个正在读取表的进程可能需要此信息，并且需要知道在较旧的时间点曾经是什么值。

Direct path writesskip REDO, UNDO, the cache, and some other features, and directly modify data files. This is a fast but potentially dangerous option in many environments, which is why there are so many confusing options to control it. Direct path writes only apply to INSERTS, and only in the scenarios described below.

直接路径写入跳过REDO、UNDO、缓存等一些功能，直接修改数据文件。在许多环境中，这是一个快速但有潜在危险的选项，这就是为什么有这么多令人困惑的选项来控制它。直接路径写入仅适用于 INSERTS，并且仅适用于以下描述的场景。

If you do nothing the default option is the safest, LOGGING.

如果你什么都不做，默认选项是最安全的，LOGGING。

The Many Ways to Control Direct Path Writes

控制直接路径写入的多种方法

LOGGING/NOLOGGING is one of several options to control direct path writes. Look at this table from AskTomto understand how the different options all work together:

LOGGING/NOLOGGING 是控制直接路径写入的几个选项之一。查看来自AskTom 的这张表，了解不同的选项如何协同工作：

Table Mode    Insert Mode     ArchiveLog mode      result
-----------   -------------   -----------------    ----------
LOGGING       APPEND          ARCHIVE LOG          redo generated
NOLOGGING     APPEND          ARCHIVE LOG          no redo
LOGGING       no append       ARCHIVE LOG          redo generated
NOLOGGING     no append       ARCHIVE LOG          redo generated
LOGGING       APPEND          noarchive log mode   no redo
NOLOGGING     APPEND          noarchive log mode   no redo
LOGGING       no append       noarchive log mode   redo generated
NOLOGGING     no append       noarchive log mode   redo generated

FORCE LOGGING can override all those settings. There are probably some other switches I'm not aware of. And of course there are the many limitations that prevent direct path - triggers, foreign keys, cluster, index organized tables, etc.

FORCE LOGGING 可以覆盖所有这些设置。可能还有一些我不知道的其他开关。当然，还有许多限制阻止直接路径 - 触发器、外键、集群、索引组织表等。

The rules are even more restrictive for indexes. An index will alwaysgenerate REDO during DML statements. Only DDL statements, like CREATE INDEX ... NOLOGGINGor ALTER INDEX ... REBUILDon a NOLOGGING index will not generate REDO.

这些规则对索引的限制更大。在 DML 语句期间，索引将始终生成 REDO。只有像NOLOGGING 索引CREATE INDEX ... NOLOGGING或ALTER INDEX ... REBUILD在 NOLOGGING 索引上的DDL 语句不会生成 REDO。

Why are there so many ways? Because recoverability is incredibly important and different roles may have different views on the matter. And sometimes some people's decisions need to override others.

为什么有这么多方法？因为可恢复性非常重要，不同的角色可能对此事有不同的看法。有时有些人的决定需要凌驾于其他人之上。

Developersdecide at the statement level, "Insert Mode". Many weird things can happen with an /*+ APPEND */hint and developers need to choose carefully when to use it.

开发人员在语句级别决定“插入模式”。许多奇怪的事情可能会随着/*+ APPEND */提示而发生，开发人员需要谨慎选择何时使用它。

Architectsdecide at the object level, "Table Mode". Some tables, regardless of how fast a developer may want to insert into it, must always be recoverable.

建筑师在对象级别决定“表格模式”。某些表，无论开发人员想多快插入其中，都必须始终是可恢复的。

Database Administratorsdecide at the database or tablespace mode, "Archive log" and FORCE LOGGING. Maybe the organization just doesn't care about recovering a specific database, so set it to NOARCHIVELOG mode. Or maybe the organization has a strict rule that everything must be recoverable, so set the tablespace to FORCE LOGGING.

数据库管理员决定在数据库或表空间模式下，“归档日志”和 FORCE LOGGING。也许组织只是不关心恢复特定的数据库，所以将其设置为 NOARCHIVELOG 模式。或者可能组织有一个严格的规则，即一切都必须是可恢复的，因此将表空间设置为 FORCE LOGGING。

回答by Durga Viswanath Gadiraju

If you have table/index with nologging, then redo will not be generated when data is inserted into the object using direct path approaches such as insert /*+ append */.

如果你有没有记录的表/索引，那么当使用直接路径方法（例如插入 /*+ 追加 */）将数据插入对象时，将不会生成重做。

However if database is in force logging mode then nologging will not have any affect. Redo is generated whether table/index is in logging or nologging mode.

但是，如果数据库处于强制日志记录模式，则 nologging 不会有任何影响。无论表/索引处于日志记录模式还是非日志记录模式，都会生成重做。

回答by J91321

If nologgingoption is set redo logs won't be generated while inserting data. You can use this to increase significantly performance of for example INSERT statement when inserting large amount of data.

如果nologging设置了选项，则在插入数据时不会生成重做日志。在插入大量数据时，您可以使用它来显着提高 INSERT 语句的性能。

Be careful never to use nologgingoption under Data guard setup. DB replication relies on redologs so it'll a create pretty big mess you certainly want to avoid.

注意不要nologging在数据保护设置下使用选项。数据库复制依赖于重做日志，因此它会造成您肯定想要避免的大混乱。