This content has been marked as final. Show 3 replies
truncate will be definitely faster than delete but it seems you want to rollback in case of failure then you are obliged to used delete (unless you save your data in a new table using CTAS and copy data back in case of need).
What do you mean you want to rollback in case of failure?
Suppose that your process fails on table 103, do you rollback all previous tables or only the latest?
If you want to rollback all previous tables, then you cannot do it with parallel processing as they will be independent job.
Please clarify your requirements.
maybe another way to go if you have enough tablespace:
create corrected_table as
UNRECOVERABLE; -- no log is generated
and generate so tables with the proper dataset (from the master-table to all child tables).
Then you can step by step delete the unnecessary data from all child tables to the master table by compare the tables with the corrected tables. So you can purge each child table as a whole independent from the master table.
Only a variation ....
Edited by: mschnatt on 04.12.2012 01:53
Thanks for your reply.
Roll back in my solution means:
Suppose process fails while deleting from 103th table, then I need to roll back all the changes i have made for that policy.
+'If you want to rollback all previous tables, then you cannot do it with parallel processing as they will be independent job.'+
What about committing the process in control program. i.e., The program which submit the jobs?
If it is possible, how can this be implemented? I only have some wild guess with me, sorry to say that.
Edited by: 974799 on Dec 4, 2012 3:46 PM