Possible to enable NOLOGGING for DML on certain tables?
672680Jul 15 2011 — edited Jul 18 2011Good afternoon,
my db version is 10gR2.
we are building a datawarehouse based on data extracting from ERP system.
today, I found that we have 250G archivelog generated for yesterday (just a single day), doing kinda heavy DML (only delete and insert) on 12 tables containing 27G data.
the thing is that, the operational data from ERP system will be always available, and as long as our SP (Stored procedures) are available, there is no need for us to generate this heavy redo log for this INSERT&DELETE operations.
In a nutshell, the fact data of this database is recoverable as long as the metadata is available.
Currently, our database is in ARCHIVELOG mode, and disabling it is not an option, since there are other applications or data that are not recoverable.
So, I wonder if there is a way to disable or reduce the redo log generation for the INSERT/DELETE operation?
any hints or suggestions and tryouts would be highly appreciated!
PS:
1) Not considering using 'append' for insert yet, since it is not encouraged to use hint in the SP. plus, the redo log generated for delete is much more than insert.
2) alter table nologging generate redo for DML and it is the DML responsible for 250G, so this option is excluded as well.
Thanks