Link to home
Start Free TrialLog in
Avatar of sakthikumar
sakthikumar

asked on

How frequently we can give commits in a plsql program?

I have a loop which consists inserts, the loop contains a commit for each record.
I have decided to commit for every 100 records,
Is there any to determine the maximum no. of records we can have for commit to have maximum efficiency, by looking at some system views, parameters etc.,
Avatar of Sean Stuber
Sean Stuber

insert as many as your rollback/undo will allow
the most I ever inserted in one transaction was a little over  900 million rows.

the exact amount you can insert will be determined by the amount of data you are inserting  (rows of 2 numbers vs rows of 20 clobs)  and triggers  that fire and indexes
For maximum efficiency you could avoid using pl/sql loops and use set processing instead i.e. insert... select..
>>>  you could avoid using pl/sql loops

agree,  if you "can" do it one sql, then do so. only use loops if you need to do some non-sql processing of each row.
Avatar of sakthikumar

ASKER

In my table, I don't have indexes, triggers, also each row has only 15 columns.

I also cannot avoid plsql loops, as it contains more non sql processing.

Now tell me what is the optimum level of rows, for which commit needs to be given.

Also, I want to know, whether this limit can be determined by any values from system views,
I mean depending upon the memory or something.......

"insert as many as your rollback/undo will allow"

How to find out this?
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial