#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    3
    Rep Power
    0

    Implicit Commit while purging.


    Hi , I have written a purge package that would delete records older than 10 years. Since the data is huge, the purging was taking 14 hours plus. To improve performance, I disabled constraints , deleted records and then reanabled them. This was quite quick but the only problem is rollback. Say for some reason if enabling constraints fails there is no way to rollback as enabling and disabling constraints does an implicit rollback. Could anybody tell how I can handle this situation.

    regards,
    Ranjit Menon
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2004
    Location
    Kurvi
    Posts
    490
    Rep Power
    186
    I don't think you should never disable constraints. I am having sort of similar issue (in thread http://forums.devshed.com/oracle-dev...ch-862247.html ) .

    The way I probably will (need to) do this is to select a part of the data, and then do periodic commits.

    The reply I got linked to http://asktom.oracle.com/pls/apex/f?...:4951966319022, according to which periodic commits are a bad idea, thou.
    Gabba Gabba Hey

IMN logo majestic logo threadwatch logo seochat tools logo