Home » SQL & PL/SQL » SQL & PL/SQL » insert using row no (oracle 9i)
insert using row no [message #425015] Wed, 07 October 2009 03:22 Go to next message
karthikeyanc2003
Messages: 33
Registered: August 2009
Location: india
Member
hi folks ,

i have a requirement like insert 1 million data from one table to other table by doing some modification the data using function on table 1. since it takes time client ask me to insert record by 1 to 1 lak , 1 lak 1 to 2 lak etc and commit on insert for each lak record. and i cannt able to get the data for select * from table x where rownum between 100001 and 2000000 .

let me know how to proceed it

Thanks in advance..

Karthikeyan C
Re: insert using row no [message #425017 is a reply to message #425015] Wed, 07 October 2009 03:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can update 1 million row in one shot you don't need to commit every X rows.
The advice is to include the function code inside the query (if it is possible).

And do it with parallelism during a maintenance window.

Regards
Michel

[Updated on: Wed, 07 October 2009 03:27]

Report message to a moderator

Re: insert using row no [message #425019 is a reply to message #425015] Wed, 07 October 2009 03:32 Go to previous messageGo to next message
karthikeyanc2003
Messages: 33
Registered: August 2009
Location: india
Member
hi Michel Cadot,
Thanks for your advice the problem is that the functions we are using to lot of java class and all and for 1 million it it take around 45 min to insert, the client was to know that the function running or not and suggest that giving the commit will remover the temp space and increase the speed.
i dont think so ..
they want the code that to be insert by l lak as i said before ....
Re: insert using row no [message #425025 is a reply to message #425019] Wed, 07 October 2009 04:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Doing it one block at a time will make it run slower.

If they just want to know that things are running, details of how far the query has progressed, and how long it is likely to take can be found in v$session_longops
Re: insert using row no [message #425034 is a reply to message #425019] Wed, 07 October 2009 04:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You likely have a primary key on your table, so you can use ntile function to divide the rows into batches of about the same size.

Regards
Michel
Re: insert using row no [message #425036 is a reply to message #425015] Wed, 07 October 2009 04:29 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
karthikeyanc2003 wrote on Wed, 07 October 2009 09:22
and i cannt able to get the data for select * from table x where rownum between 100001 and 2000000 .


You should also read up on how rownum actually works because that approach is flawed.
I wouldn't use rownum for this mind.

EDIT: added last sentence

[Updated on: Wed, 07 October 2009 04:30]

Report message to a moderator

Re: insert using row no [message #425043 is a reply to message #425015] Wed, 07 October 2009 05:07 Go to previous messageGo to next message
karthikeyanc2003
Messages: 33
Registered: August 2009
Location: india
Member
hi thanks for all of your replies
Re: insert using row no [message #425050 is a reply to message #425015] Wed, 07 October 2009 05:31 Go to previous messageGo to next message
cherry
Messages: 56
Registered: December 2007
Member
you can restrict the number of rows you select using rownum:
scott@TESTDB> select * from
  2  (select rownum as rn, group_of_rec.* from
  3  (select * from your_table) group_of_rec
  4  where rownum <=5)
  5  where rn >=1 and <= 100,001
  6  /

or using plsql: this one was from Tom I guess.
 declare
      type array is table of number index by binary_integer;
      l_data array;
      cursor c1 is select * from your_table;
  begin
      open c1;
      loop
          fetch c1 bulk collect into l_data limit 5;
          /* process data here */
		 forall i in 1 .. l_data.count
		  insert into insert_table (cola)
		  values (l_data(i));
          dbms_output.put_line( 'looping, c1%rowcount = ' || c1%rowcount );
          exit when c1%notfound;
      end loop;
      dbms_output.put_line( 'exiting loop, c1%rowcount = ' || c1%rowcount );
      close c1;
  end;

[Updated on: Wed, 07 October 2009 05:33]

Report message to a moderator

Re: insert using row no [message #425053 is a reply to message #425050] Wed, 07 October 2009 05:47 Go to previous message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
cherry wrote on Wed, 07 October 2009 11:31
you can restrict the number of rows you select using rownum:
scott@TESTDB> select * from
  2  (select rownum as rn, group_of_rec.* from
  3  (select * from your_table) group_of_rec
  4  where rownum <=5)
  5  where rn >=1 and <= 100,001
  6  /



If you're going to use that approach then the inner query needs an order by - otherwise you'll get random results.

EDIT: fixed tags

[Updated on: Wed, 07 October 2009 05:48]

Report message to a moderator

Previous Topic: Problem with getting IP address ....
Next Topic: Need opinion in using TO_CHAR
Goto Forum:
  


Current Time: Sat Dec 10 20:36:27 CST 2016

Total time taken to generate the page: 0.08079 seconds