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 ;                 =20
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]=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,
>=20
> Scenario overview
> ------------------
>=20
> 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...=20

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




----------------------------------------------------------------
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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: