Search My Oracle Blog

Custom Search

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

4 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

Oluwatobi Ogunsola 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

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016