Commits in PL SQL
703011May 22 2009 — edited May 22 2009Hi everyone,
May I know if there is a maximum or optimum amount of transactions that can take place before a commit is executed?
My problem is caused by the commit command not allowing the use of WHERE CURRENT OF in a loop to update the current record. This results in a need to search through the large table (3 million records) again in order for the current record to be updated.
(A side note is that using WHERE CURRENT OF reduce the run time of the script from 20 mins to 1 min for a 10k test case. A linear calculation means 3 million records is gong to take 10 days...)
I am suggesting to my supervisor to commit only after the end of the loop, but I need some technical information to explain whether it is better to commit after the loop or commit after every record. What is the advantage and disadavantage of the both of them?
Thanks in advance and sorry that I can't put the original script up due to confidentiality issues...
Regards,
phyrexians