KuldeepReddy
asked on
Purging OLD data from oracle database
Hi Experts,
We have a huge OLTP database with oracle 10g. It has data from year 2000 and it is causing performance issues so I’m writing a purge process to clean old data from database. We can’t afford outage to this task so I had written PLSQL code to delete the OLD data. There are 13 tables need to be purged and having parent and child relationship. This is how I have written the code.
Step1: go and grab one chunk (3000) profiles from parent table and load into a one temp table.
Step 2: delete these profiles from child tables. At the end, we are deleting from parent table.
Step 3: commit the data
Step 4: repeat steps 1, 2 and 3 until there are no profile matches deletion criteria.
Here is sample code I have.
Begin
loop
Insert into temp (tid)
Select p.id from parent p where date<=cutoff_date and rownum<=3000;
Exit when sql%rowcount=0;
Delete child1 c1
where exists (select * from temp t1 where c1.id=t1.id);
…
Delete child13 c13
where exists (select * from temp t1 where c13.id=t1.id);
commit;
end loop;
end;
There are 330M profiles are there need to be purged so it will take long time (3+ months) to delete these profiles and generating gobs of redo. Business wants to purge this data quicker.
In some forums, I have seen approach of just retaining required data. Basically, it says that creating a one temp table holds required database. Then we truncate source table and copy data from temp table. The problem with this approach is that it requires outage which we can’t afford it. Also, we need to same thing for 13 tables. I presume that it’s going to take extended outage.
I just want to find out is there any fast way purging the data. I have been looking for this since couple of weeks but no luck. Any help really appreciated.
We have a huge OLTP database with oracle 10g. It has data from year 2000 and it is causing performance issues so I’m writing a purge process to clean old data from database. We can’t afford outage to this task so I had written PLSQL code to delete the OLD data. There are 13 tables need to be purged and having parent and child relationship. This is how I have written the code.
Step1: go and grab one chunk (3000) profiles from parent table and load into a one temp table.
Step 2: delete these profiles from child tables. At the end, we are deleting from parent table.
Step 3: commit the data
Step 4: repeat steps 1, 2 and 3 until there are no profile matches deletion criteria.
Here is sample code I have.
Begin
loop
Insert into temp (tid)
Select p.id from parent p where date<=cutoff_date and rownum<=3000;
Exit when sql%rowcount=0;
Delete child1 c1
where exists (select * from temp t1 where c1.id=t1.id);
…
Delete child13 c13
where exists (select * from temp t1 where c13.id=t1.id);
commit;
end loop;
end;
There are 330M profiles are there need to be purged so it will take long time (3+ months) to delete these profiles and generating gobs of redo. Business wants to purge this data quicker.
In some forums, I have seen approach of just retaining required data. Basically, it says that creating a one temp table holds required database. Then we truncate source table and copy data from temp table. The problem with this approach is that it requires outage which we can’t afford it. Also, we need to same thing for 13 tables. I presume that it’s going to take extended outage.
I just want to find out is there any fast way purging the data. I have been looking for this since couple of weeks but no luck. Any help really appreciated.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The rename table approach means you also need to recreate all indexes/constraints.
ASKER
Hi experts,
For now, let's look at the existing code i have developed to delete 'N' rows at a time. I have actual code attached which will delete 'N' number of rows at a time from child table and delete same rows parent table finally. Please suggest how we can update PLSQL block to improve better ptocessing rate (No of rows deleted per second). Any help really appreaciated.
Please let me know if you have questions/concerns.
Thanks.
code.txt
For now, let's look at the existing code i have developed to delete 'N' rows at a time. I have actual code attached which will delete 'N' number of rows at a time from child table and delete same rows parent table finally. Please suggest how we can update PLSQL block to improve better ptocessing rate (No of rows deleted per second). Any help really appreaciated.
Please let me know if you have questions/concerns.
Thanks.
code.txt
On mobile right now and cannot get a complete look at the code but I suggest you generate explain plans for the individual pieces.
For example, do you have an index on lastactivity_date? That might speed up the first query.
Then on the temp tables, what indexes do you have? You might need to use index hints or regenerate statistics on the temp tables after large inserts to ensure efficiency.
For example, do you have an index on lastactivity_date? That might speed up the first query.
Then on the temp tables, what indexes do you have? You might need to use index hints or regenerate statistics on the temp tables after large inserts to ensure efficiency.
i agree with slightwv
you will have to do this slow and just delete a small amount of dataevery time
this will be a long process but will work with minimum overhead and risk to your system.