06 February, 2010

Some Common Errors - 2 - NOLOGGING as a Hint

Continuing the Common Errors Series,

this is the second common error : specifying NOLOGGING as a Hint and expecting it to work.

I have seen instances of code like :
INSERT /*+ APPEND NOLOGGING */ INTO 

OR
INSERT /*+ NOLOGGING */ INTO 

OR
DELETE /*+ NOLOGGING */ FROM 

OR
UPDATE /*+ NOLOGGING */ 


All of these will generate Redo -- i.e. none of them will actually be NOLOGGING.
(update : caveat : The INSERT /*+ APPEND NOLOGGING */ will actually be a nologging operation in a database that is running NOARCHIVELOG mode, but not because of the NOLOGGING hint but because of how Oracle handles APPEND in a NOARCHIVELOG database).

There is no such Hint as NOLOGGING.
NOLOGGING can only be specified either as a
a) Table/Index attribute (e.g with an "ALTER TABLE/INDEX")
or
b) as a DDL Clause (in a CREATE or ALTER ... MOVE or ALTER .. REBUILD)

If you specify a "Hint" "NOLOGGING" Oracle calmly ignores it -- intended hints that are invalid are ignored (or, you could say, as I would, treated as comments) without raising an error.

This still raises questions about NOLOGGING in situations like
1. When it is defined at the Tablespace/Table/Index level -- does it apply to all operations ?
2. How does NOLOGGING come into play with respect to Indexes.

Needless to say, there seem to be some misconceptions around these as well. I shall raise those as other "common errors" in the near future.


UPDATE : If you do use the NOLOGGING as Hint, please take note of Oracle Support Note 826893.1.   Any Hint after the invalid NOLOGGING Hint is ignored.  Thus, in this case :
INSERT /*+ NOLOGGING PARALLEL (4) */ ....

the PARALLEL Hint is NOT used. The Insert operates in ignorance of the PARALLEL Hint -- it becomes a Serial Insert.  Similarly, any other Hint after the NOLOGGING is also ignored.
.
.
.

8 comments:

Ji Li said...

Thank you for sharing your knowledge Hemant. That is good to know since I thought using the NOLOGGING hint would avoid the use of the redo logs.

Is there any possible way to avoid the redo logs without using truncate?

I can not alter the table to perform the operation before and after, so I need a way to do it dynamically when I run my command (delete from table).

Hemant K Chitale said...

To remove rows from a table, Oracle provides only two choices : DELETE (which *does* generate redo) and TRUNCATE.

However, another choice is to create a new table and copy only the desired rows (with APPEND) to the new table. Then rename and/or drop the old table and rename the new table.
You have to also build the appropriate indexes on the new table as are currently defined.


Hemant K Chitale

Tobi's Notes said...

alter table test_log nologging;

make sure you revert back, if need. note setting logging has other implications

Hemant K Chitale said...

Oluwatobi,

Only Direct Path INSERTs are NOLOGGING operations. Normal INSERTs and all UPDATEs and DELETEs will generate redo even if the table is set to NOLOGGING.

I am perfectly happy leaving tables with the NOLOGGING attribute.


Hemant K Chitale

Unknown said...

Are there any changes for the Oracle R12 (12.0.5 db) version regrading to the NOLOGGING?
Thanks,
Biti

Hemant K Chitale said...

Biti Ranjan,
I am not sure if you are referring to EBS R12 or Database R12c. In either case, the NOLOGGING Hint behaviour remains the same in 10g, 11g and 12c.

TomS_Ott said...

This statement of your seems incoompelete:
"INSERT /*+ APPEND NOLOGGING */ will actually be a nologging operation in a database that is running NOARCHIVELOG mode, but not because of the NOLOGGING hint but because of how Oracle handles APPEND in a NOARCHIVELOG database).
"

it should end in " ... not because of the NOLOGGING hint but because of how Oracle handles INSERT with the APPEND hint in a NOARCHIVELOG database"

Hemant K Chitale said...

TomS_Ott,

NOLOGGING is in the context of INSERT -- so I don't need to specify "handles INSERT"

Similarly, APPEND is in the context of INSRT -- so I don't need to specify "INSERT with the APPEND"

I am relating APPEND with the database mode of NOARCHIVELOG here.