Hello,
It is my understanding that the nologging setting is limited and does not apply when used with certain DML operations specifically deletes. I found this in the documentation for truncating partitions :
https://docs.oracle.com/cd/E11882_01/server.112/e25523/part_admin002.htm#i1008226
...also in :
Common Questions About Dropping/Truncating Partition (Doc ID 1483661.1)
Truncating a Partition Containing Data and Referential Integrity Constraints
If a partition contains data and has referential integrity constraints, then you cannot truncate the partition. If no other data is referencing any data in the partition to remove, then choose either of the following methods to truncate the table partition.
Method 1
Disable the integrity constraints, run the ALTER TABLE ... TRUNCATE PARTITION statement, then re-enable the integrity constraints. This method is most appropriate for large tables where the partition being truncated contains a significant percentage of the total data in the table. If there is still referencing data in other tables, then you must remove that data to be able to re-enable the integrity constraints.
Method 2
Issue the DELETE statement to delete all rows from the partition before you issue the ALTER TABLE ... TRUNCATE PARTITION statement. The DELETE statement enforces referential integrity constraints, and also fires triggers and generates redo and undo logs. Data in referencing tables is deleted if the foreign key constraints were created with the ON DELETE CASCADE option.
Note:
You can substantially reduce the amount of logging by setting the NOLOGGING attribute (using ALTER TABLE ... MODIFY PARTITION ... NOLOGGING) for the partition before deleting all of its rows.
DELETE FROM sales partition (dec94);
ALTER TABLE sales TRUNCATE PARTITION dec94;
This method is most appropriate for small tables, or for large tables when the partition being truncated contains a small percentage of the total data in the table.
Please note the line I highlighted in RED. I am wondering if this is a valid statement since it is contrary to what I know about the nologging attribute.
Does anyone have any experience with using nologging on partitions for delete statements? Any advice would be appreciated.
Thanks,
CMai