What is the purpose of logging/nologging option in Oracle

Question

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?

Answer 1

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.

Oracle Architecture Background Information

REDO is 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.

UNDO helps 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 used to be at an older point-in-time.

Direct path writes skip 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.

If you do nothing the default option is the safest, 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 AskTom to understand how the different options all work together:

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.

The rules are even more restrictive for indexes. An index will always generate REDO during DML statements. Only DDL statements, like CREATE INDEX ... NOLOGGING or ALTER INDEX ... REBUILD on a NOLOGGING index will not generate 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.

Developers decide 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.

Architects decide 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 Administrators decide 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.

Answer 2

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.