But I think offset is calculated only once as it is outside the loop.... -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mark W. Farnham Sent: Wednesday, June 23, 2004 10:57 AM To: oracle-l@xxxxxxxxxxxxx Subject: RE: Performance question you might want to use a sequence to keep track of the offset so you're not reading what you're writing all the time. -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Harvinder Singh Sent: Wednesday, June 23, 2004 10:47 AM To: ryan.gaffuri@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx Subject: RE: Performance question create or replace procedure insert_values as id_sess_offset integer; cursor c1 is select ID_SESS + id_sess_offset id_sess,....other columns ID_ACC from user1.tab1; type tab_typ_ID_SESS is table of user1.tab1.ID_SESS%type index by binary_integer ; type tab_typ_ID_ACC is table of user1.tab1.ID_ACC%type index by binary_integer ; =3D20 other columns............ v_ID_SESS tab_typ_ID_SESS ; v_ID_ACC tab_typ_ID_ACC ; other columns......... begin select nvl(max(id_sess), 0)+1 into id_sess_offset from user2.tab2; open c1; loop fetch c1 bulk collect into v_ID_SESS,v_ID_ACC limit 1000 ;other columns exit when c1%notfound; forall i in 1..1000 insert into user2.tab2 values(v_ID_SESS(i),v_ID_ACC(i); ..other columns commit; end loop; close c1; end; / -----Original Message----- From: ryan.gaffuri@xxxxxxxxxxx [mailto:ryan.gaffuri@xxxxxxxxxxx]=3D20 Sent: Wednesday, June 23, 2004 10:33 AM To: oracle-l@xxxxxxxxxxxxx Cc: Harvinder Singh; oracle-l@xxxxxxxxxxxxx Subject: Re: Performance question comments inline....=3D20 > Hi, >=3D20 > Scenario overview > ------------------ >=3D20 > We are testing an application prototype and inserting 1000 rows at a > time from 1 table and populating into 2nd table in a loop and inserting > 1M rows. 2nd table have primary key on 2 column, 1 column unique key and > 2 non-unique indexes each having 6 columns. This is 10g on WIN2K. This is not exactly clear...=3D20 are you doing the following For x in (select stuff from table) insert 1000 rows end loop commit; you are stating you can do 30,000 of those per second at one point? Please post pseudo-code. Please do NOT post all of the real code, just an outline.=3D20 ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------