Ajoy George, July 05, 2016 - 1:33 pm UTC
Chris,
We are using Data guard to manage DR.
When I said "DMLs mostly not required", I meant we do not need the REDO logs for same as its just the data that is to be transferred to another system via a interface and deleted in 3 days.
My worry here is if I change the table to NOLOGGING and add APPEND hint:
1. there will be periodic DELETEs(to remove data already pushed via interface job), will it cause lot of unclaimed space. Is it possible to bring back High Water Mark through index rebuild? There are no other conventional INSERTS happening on the table.
2. would I be able to similarly avoid creation of REDO logs during DELETE and UPDATE using APPEND hint?
3. is it possible to alter to NOLOGGING at a query level instead of table level? or any better approach available in 11g?
Thanks,
July 05, 2016 - 4:44 pm UTC
"I meant we do not need the REDO logs".
Well, if you want to apply the changes at your DR site, then yes, you do need the redo!
If you're looking to minimize the redo, there are other options such as:
- Do the delete as a "create table as select"
- Partition the table. Then instead of deleting the data it's a truncate/drop partition operation. This will be far less redo than the delete.
Investigate these!
1. The table high water mark is unrelated to indexes. You'll need to move the table to reclaim this space.
2. Append only applies for inserts.
3. No. And if your database is in force logging mode this will ensure you always log.