RE: Performance question

  • From: "Harvinder Singh" <Harvinder.Singh@xxxxxxxxxxxxx>
  • To: <ryan.gaffuri@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 23 Jun 2004 10:46:46 -0400

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 ;                 =20
other columns............
    v_ID_SESS           tab_typ_ID_SESS          ;
    v_ID_ACC            tab_typ_ID_ACC           ;
other columns.........
select nvl(max(id_sess), 0)+1 into id_sess_offset from user2.tab2;
    open c1;
    fetch c1 bulk collect into v_ID_SESS,v_ID_ACC limit 1000 ;other
        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;

-----Original Message-----
From: ryan.gaffuri@xxxxxxxxxxx [mailto:ryan.gaffuri@xxxxxxxxxxx]=20
Sent: Wednesday, June 23, 2004 10:33 AM
To: oracle-l@xxxxxxxxxxxxx
Cc: Harvinder Singh; oracle-l@xxxxxxxxxxxxx
Subject: Re: Performance question

comments inline....=20

> Hi,
> Scenario overview
> ------------------
> 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
> 1M rows. 2nd table have primary key on 2 column, 1 column unique key
> 2 non-unique indexes each having 6 columns. This is 10g on WIN2K.

This is not exactly clear...=20

are you doing the following

For x in (select stuff from table)
   insert 1000 rows
end loop

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

Please see the official ORACLE-L FAQ:
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at

Other related posts: