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
INSERT /*+ NOLOGGING */ INTO
DELETE /*+ NOLOGGING */ FROM
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")
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.