Storedprocedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Storedprocedure

  1. #1
    Join Date
    Dec 2002
    Location
    Singapore
    Posts
    27

    Storedprocedure

    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
    Attached Files Attached Files

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Dec 2002
    Location
    Singapore
    Posts
    27
    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

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    The quickest way to load huge volume of data is doing it in parallel.
    Use PDML option of 9i/10G.

    PL/SQL is not designed for data load.

    Tamil

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width