0

I was wandering what is the practical purpose of using the NOLOGGING clause on tables and indexes.

For what I know from the Oracle documentation, we can prevent the database from generating redo logs only when INSERTING in direct-path mode. There is no possibility to avoid redo log generation when UPDATING or DELETING.

So, we usually write

INSERT /*+ APPEND NOLOGGING */ INTO TABLE ... ;
commit;

in order to prevent redo log generation.

I totally don't get what is the practical advantage of using the NOLOGGING clause on TABLE or INDEX creation neither to use the NOLOGGING clause on TABLESPACES creation in order to set the default on the objects that will be created on that tablespace.

Can anyone describe a practical scenario where setting this clause on database objects may give advantages?

2

There is no such hint as NOLOGGING.

Sure, UPDATE and DELETE operations can not take advantage of NOLOGGING, but there are other operations that can:

logging_clause

NOLOGGING is supported in only a subset of the locations that support LOGGING. Only the following operations support the NOLOGGING mode:

DML:

Direct-path INSERT (serial or parallel) resulting either from an INSERT or a MERGE statement. NOLOGGING is not applicable to any UPDATE operations resulting from the MERGE statement.

Direct Loader (SQL*Loader)

DDL:

CREATE TABLE ... AS SELECT (In NOLOGGING mode, the creation of the table will be logged, but direct-path inserts will not be logged.)

CREATE TABLE ... LOB_storage_clause ... LOB_parameters ... CACHE | NOCACHE | CACHE READS

ALTER TABLE ... LOB_storage_clause ... LOB_parameters ... CACHE | NOCACHE | CACHE READS (to specify logging of newly created LOB columns)

ALTER TABLE ... modify_LOB_storage_clause ... modify_LOB_parameters ... CACHE | NOCACHE | CACHE READS (to change logging of existing LOB columns)

ALTER TABLE ... MOVE

ALTER TABLE ... (all partition operations that involve data movement)

ALTER TABLE ... ADD PARTITION (hash partition only)

ALTER TABLE ... MERGE PARTITIONS

ALTER TABLE ... SPLIT PARTITION

ALTER TABLE ... MOVE PARTITION

ALTER TABLE ... MODIFY PARTITION ... ADD SUBPARTITION

ALTER TABLE ... MODIFY PARTITION ... COALESCE SUBPARTITION

CREATE INDEX

ALTER INDEX ... REBUILD

ALTER INDEX ... REBUILD [SUB]PARTITION

ALTER INDEX ... SPLIT PARTITION

| improve this answer | |
  • Thanks for helping me dispelling the "NOLOGGING Sql hint" myth. Your point helped me in discovering further serious implications as reported in MOS Bug ID 8432870 - NOLOGGING HINT IN 10G CAUSES HINTS AFTER IT TO BE IGNORED. I suggest give it a read... – Massimo Pasquini Oct 2 '19 at 12:31
1

The NOLOGGING mechanism, as you pointed out, is to avoid the generation of redo logging information, and that can make bulk loading faster.

I use it typically for big loading jobs. For example, loading terabytes of satellite imagery: it means that I will write TB of bits to the database proper (the data files), but that I will also write terabytes to the redo logs. This means additional I/Os on the logs, as well as a lot of disk space for the archive logs.

By disabling logging during that load, I avoid those extra I/Os and space.

Of course, it means that, should I have a total media loss (= one disk drive fails irremediably), then I'm unable to recover: I can recover the media from a backup, but I'm unable to roll forward the failed load. That is fine: it is faster to just restart the load from the last commit point than to have the database plod its way through TB and TB of logs.

So, essentially, nologging makes sense if:

  1. You know for a fact that it will speed up the load.
  2. You are able to easily restart/continue from any failure (like: just redo the CREATE TABLE AS SELECT or INSERT SELECT
| improve this answer | |
  • That is absolutely clear. What was not is that NOLOGGING is not a SQL hint, as pointed out by @Balazs Papp. – Massimo Pasquini Oct 2 '19 at 10:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.