Avatar of 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.,
Oracle DatabaseSQL

Avatar of undefined
Last Comment

8/22/2022 - Mon
Sean Stuber

insert as many as your rollback/undo will allow
Sean Stuber

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..
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Sean Stuber

>>>  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.

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?
Sean Stuber

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.