CODEWITHSUNDEEP
databaseoracle-databaseplsqlquery-optimizationsql-insert
Reza Davoudian
Reza Davoudian

Reputation: 155

Commit insert command per each 1 million rows

I have a table tbl_1 (in oracle database) with more than 1 billion records. I want to insert all the records in a new table tbl_2. If I use:

insert into tbl_2 select * from tbl-1;
commit;

then the code takes a long time to done because oracle DBMS must consider online redo log files and zero data loss.

An idea is to commit insert per each 1 million records therefor online redo log files handling and other background processes applied by DBMS take less time. An answer is using cursor but I guess using cursor makes the code slow. Do you have any idea?

Upvotes: 0

Views: 903

Answers (1)

Alex Poole
Alex Poole

Reputation: 191455

Quoting Tom Kyte:

There is only one thing that dicates when you commit -- when is your transaction complete? Then you commit, period.

and

Frequently commiting in the mistaken belief that you are conserving resources or making things go faster only does this for you:

  • slows you down, yes, that is right, SLOWS YOU DOWN -- makes you run SLOOOWWWWEEERRR
  • doesn't conserve any resources, in fact, it consumes more (you generate MORE redo)
  • seriously puts into question the integrity of your data

and (earlier in that thread):

Frequent commits -- sure, "frees up" that undo -- which invariabley leads to ORA-1555 and the failure of your process. Thats good for performance right?

Frequent commits -- sure, "frees up" locks -- which throws transactional integrity out the window. Thats great for data integrity right?

Frequent commits -- sure "frees up" redo log buffer space -- by forcing you to WAIT for a sync write to the file system every time -- you WAIT and WAIT and WAIT. I can see how that would "increase performance" (NOT). Oh yeah, the fact that the redo buffer is flushed in the background

  • every three seconds
  • when 1/3 full
  • when 1meg full would do the same thing (free up this resource) AND not make you wait.

frequent commits -- there is NO resource to free up ... etc.

A single big insert and single commit will be the fastest option.

There might be things you can do to speed things up a bit, such as disabling constraints and triggers, dropping indexes and recreating after the insert, direct-path inserts, etc. but it depends on how you have things set up now.

Upvotes: 3

Related Questions