We have a master table (policy) and 104 associated tables in our data base. We need to purge data from these tables based on several factors.
Identified solution option :
• Create an offline schema and associated table spaces. This schema will be read-only with restricted access.
• Develop PLSQL programs to identify, copy and delete records based on the criteria
We are proceeding with this, even though purge may get influenced by certain overheads inherent due to the use of the DELETE statement, as deletes generates rollback information for Oracle to recover in case of failure and this rollback processing cannot be bypassed.
My doubt :
Once we identify the policy number from master table, we need to search 104 tables for related records.
As per my script, this occurs serially. i.e. Search one table, then other and so on.
Is there any way to do this parallelly ? i.e. Search the all tables simultaneously.
For e.g. : kicks off number of parallel running stream via DBMS_JOBS and handle them.
Kindly note: We are using Oracle 9i.
Any hint will be greatly appreciated !
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.
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.
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.