Home » SQL & PL/SQL » SQL & PL/SQL » Intermittent commit while inserting
Intermittent commit while inserting [message #232699] Mon, 23 April 2007 01:19 Go to next message
abcindiaxyz
Messages: 13
Registered: March 2005
Location: Indonesia
Junior Member
Dear All,
Hi.
My requirement is to commit the data intermittently while inserting.

Say for example there are 50,000 rows in a table "abc", and now i want to insert these 50,000 rows into another table "xyz", but the commit should happen every 5,000 records.

My requirement is urgent, so please help me in this regard as soon as possible.
Re: Intermittent commit while inserting [message #232715 is a reply to message #232699] Mon, 23 April 2007 01:30 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
Why do you want to commit every 5000 rows?

If something goes wrong after the first commit you can only roll-back the last commit.
Re: Intermittent commit while inserting [message #232719 is a reply to message #232699] Mon, 23 April 2007 01:44 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
abcindiaxyz wrote on Mon, 23 April 2007 08:19
Say for example there are 50,000 rows in a table "abc", and now i want to insert these 50,000 rows into another table "xyz", but the commit should happen every 5,000 records.
Why do you want to commit every 5000 records? You love ORA 1555? You detest performance and you want your code to run a tad slower?

Seriously, why would you want that? 50000 records is not a lot.

MHE
Re: Intermittent commit while inserting [message #232745 is a reply to message #232699] Mon, 23 April 2007 03:01 Go to previous messageGo to next message
kc1982
Messages: 13
Registered: October 2006
Junior Member
DECLARE
  commit_number := 0;
BEGIN
  INSERT INTO abc (xxx) values (yyy);
  commit_number:= commit_number+ 1;
  IF MOD(commit_number, 5000) = 0 Then
   commit;
  END IF;
END;

[Updated on: Mon, 23 April 2007 03:02]

Report message to a moderator

Re: Intermittent commit while inserting [message #232747 is a reply to message #232745] Mon, 23 April 2007 03:03 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I'm sorry but please review your code. This does not make sense.

MHE
Re: Intermittent commit while inserting [message #232751 is a reply to message #232699] Mon, 23 April 2007 03:21 Go to previous messageGo to next message
kc1982
Messages: 13
Registered: October 2006
Junior Member
sorry, didn't read the message well.

DECLARE
 CURSOR TEST IS
  SELECT COLUMN_1 FROM ABC;
 COMMIT_NUMBER NUMBER := 0; 
BEGIN
 FOR CUR_REC IN TEST LOOP
  INSERT INTO XYZ (COLUMN1) VALUES (CUR_REC.COLUMN_1);
  COMMIT_NUMBER:=COMMIT_NUMBER+1;
  IF MOD(COMMIT_NUMBER, 5000) = 0 THEN
    COMMIT;
  END IF;
 END LOOP;
END;
Re: Intermittent commit while inserting [message #232755 is a reply to message #232751] Mon, 23 April 2007 03:32 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
That's a poor, very poor, design. The entire idea is so bad. Not your coding "an sich" but the fact that one wants to commit within a loop, especially with open cursors. Fetch across commit is not what you want.

Tom Kyte agrees.

MHE
Re: Intermittent commit while inserting [message #232757 is a reply to message #232755] Mon, 23 April 2007 03:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Here's a thought - instead of doing a commit, why not set a savepoint, and then add an exception block that will do a ROLLBACK TO SAVEPOINT, and then continue inserting. That way if you do get errors in the insert you won't loose everything (which is I guess why you want to do this) and it will still be better than Commiting.

It'll still be slower than just doing one single SQL insert statement though.
Re: Intermittent commit while inserting [message #232869 is a reply to message #232699] Mon, 23 April 2007 13:39 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
If you are on Oracle 10g, simply use the error logging clause. This will allow you to continue past errors. See

http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm#BGBEIACB
Re: Intermittent commit while inserting [message #242498 is a reply to message #232699] Sat, 02 June 2007 20:08 Go to previous messageGo to next message
abcindiaxyz
Messages: 13
Registered: March 2005
Location: Indonesia
Junior Member
Here is the final query i used (modified a bit to hide my tables).


DECLARE
INSERT_COUNT NUMBER := 1;
CURSOR c1 IS SELECT /*+ PARALLEL ( SEL_TBL 2) FULL (SEL_TBL) */ * from schema_name1.table_name1 SEL_TBL;
BEGIN
FOR r1 IN c1 LOOP
INSERT /*+ PARALLEL ( INS_TBL 2) APPEND */ INTO schema_name1.table_name1 INS_TBL (columns) VALUES (values);
-- commit after every 100 inserts to avoid large rollback segments
IF INSERT_COUNT = 100 THEN
COMMIT;
INSERT_COUNT := 1;
ELSE
INSERT_COUNT := INSERT_COUNT + 1;
END IF;
END LOOP ;
COMMIT;
END
;
/
Commit ;










THANKS all for your replies
Re: Intermittent commit while inserting [message #242499 is a reply to message #232699] Sat, 02 June 2007 20:34 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
As Maaher said in message #232719 WHEN (not if) you get ORA-01555 error, please do not bother posting here asking for help.
The answer will be "We told you so. You have met the enemy & they is you."
Re: Intermittent commit while inserting [message #242507 is a reply to message #242498] Sun, 03 June 2007 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
INSERT /*+ PARALLEL ( INS_TBL 2) APPEND */ INTO schema_name1.table_name1 INS_TBL (columns) VALUES (values);

This is the same thing as:
INSERT /*+ Oracle don't care about what you put there */ INTO schema_name1.table_name1 INS_TBL (columns) VALUES (values);

"SELECT * FROM tab;" with a FULL hint a non-sense. What can Oracle do if not a FULL SCAN?

If you don't know what are and when to use hints, DO NOT USE THEM.

I still don't understand why you're not using something like:
INSERT INTO ... SELECT ... FROM ...;

Regards
Michel

[Updated on: Sun, 03 June 2007 01:05]

Report message to a moderator

Re: Intermittent commit while inserting [message #242510 is a reply to message #242507] Sun, 03 June 2007 03:03 Go to previous messageGo to next message
abcindiaxyz
Messages: 13
Registered: March 2005
Location: Indonesia
Junior Member
Dear Michel,
What you said is to an extent OK, but start thinking BIG.
I was using a table with ~2.5million records over a network and you cannot expect to use your "INSERT INTO ...........SELECT ............", right, so i used it.
Anyways, thanks for the comments

[Updated on: Sun, 03 June 2007 03:05]

Report message to a moderator

Re: Intermittent commit while inserting [message #242523 is a reply to message #242507] Sun, 03 June 2007 09:45 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Good point about the hints - they are doing nothing here. Neither parallel nor direct-path make any sense in a single-row INSERT, and so both hints will be ignored.

Also agree regarding the inadvisability of committing in loops. If your database can't handle 2.5 million rows in one INSERT statement it's probably time to get a new laptop. And even if for some bizarre reason you absolutely HAVE to do the processing in a loop, you could still use bulk collect and FORALL to retain some chance of reasonable performance.
Re: Intermittent commit while inserting [message #242536 is a reply to message #242510] Sun, 03 June 2007 13:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table t as select * from dba_objects where 1=0;

Table created.

SQL> set timing on
SQL> insert /*+ APPEND */ into t select a.* from dba_objects a, dba_objects b where rownum <=2500000;

2500000 rows created.

Elapsed: 00:01:06.10

1 minute 6 seconds on my laptop which parameters are:
OS: Windows XP Pro (Version 5.01.2600)
   Service Pack 2 (2.0)

Processor: Intel Pentium M (0.09)
   (Intel(R) Pentium(R) M processor 1.86GHz)
   1 Processor(s) 1861MHz
   Cache: L1code=32K L1data=32K L2=2048K 

Memory (Available Ko) :
   Physical: 256.112 / 1.047.960 (24%)
   Paging: 1.618.492 / 2.519.400 (64%)

Not the top, doesn't it?

Btw, all is done on the server, there is no relation with network in a SQL query (unless you query a remote table, is this your case?).

Regards
Michel

Re: Intermittent commit while inserting [message #242546 is a reply to message #242536] Sun, 03 June 2007 23:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I think William was being sarcastic here. I think he meant to say that if you can't insert 2.5 mln records without a commit, your database will most likely run on your laptop...
Re: Intermittent commit while inserting [message #242551 is a reply to message #242546] Mon, 04 June 2007 00:56 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Frank,
I didn't answer to William's post, I wanted to strenghten it showing that it is more than a sarcarsm, it is reality, even with not the latest laptop model.

Regards
Michel
Previous Topic: don't Have access to permission for the procedure
Next Topic: Stored procedure resultsets
Goto Forum:
  


Current Time: Tue Dec 06 00:00:09 CST 2016

Total time taken to generate the page: 0.11185 seconds