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 :
OR
OR
OR
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 :
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.
.
.
.
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:
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).
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
alter table test_log nologging;
make sure you revert back, if need. note setting logging has other implications
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
Are there any changes for the Oracle R12 (12.0.5 db) version regrading to the NOLOGGING?
Thanks,
Biti
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.
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"
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.
Post a Comment