Hi All,
I Would like to have some feedback on this
storedprocedure.please do find enclosed the attached
Testprocedure.sql.This storedprocedure is for moving
data from normal table to History table.But as the no.
of records in the tables are quite huge i'm having a
doubt whether this procedure will work fine as it is
inserting and deleting records.The approx. no of
records need to be moved is around 50 MILLION.This is a test
table.i have similarly 6 to 8 tables to be moved.Can any
one make any modifications to this procedure so as to
enable a smooth execution.What other steps i need to
incorporate in this procedure so that i don't hit
across any other error when executing.can i do a one
shot commit or any other suggestions to optimise the
procedure is greatly apprecited.This process is for
our housekeeping job for which we need to move data
say from last 4 years onwards and move it to history tables and simultaneously delete records from normal table.
Thanks for ur time
Rgds,
Ram
This row-by-row processing is going to kill you, because even if you deal with one row in a hundredth of a second, 50 million of those is going to add up to over five days. The only way to make this perform is to use SQL statements to move the entire data set in one operation ...
Code:
Insert /*+ append */ Into HIS_KRIS
Select * from ...
... then a similar approach for the deletes -- one statement.
Better than that would be to utilize the partitioning option. With a table range partitioned on a column defined as NVL(LCHG_DT,RCRE_DT) you can make this process so fast that it'd make your eyes bulge.
Hi Slimdave,
Thanks for ur valuable comments.Do you mean to say to use the Insert /*+ append */into HIS_KRIS VALUES(r.ENAME,r.RCRE_DT,r.LCHG_DT,r.RCRE_USER_ID) and
Similar delete /*+ APPEND */from kris where current of OLD_DATA;
using the cursor as i have written in the procedure.And can u highlight to me on how can i move it through partitioning as you have mentioned.My table is already partitioned and the corresponding HIS_table is also partitioned.Your suggestions are greatly appreciated.
Thanks,
Ram