RE: Performance question

From: Harvinder Singh <Harvinder.Singh_at_MetraTech.com>
Date: Wed, 23 Jun 2004 10:46:46 -0400
Message-ID: <D6424CD4C8A3C044BBC49877ED51C518C2C9E8@ex2003.metratech.com>

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_at_comcast.net [mailto:ryan.gaffuri_at_comcast.net]=20 Sent: Wednesday, June 23, 2004 10:33 AM
To: oracle-l_at_freelists.org
Cc: Harvinder Singh; oracle-l_at_freelists.org 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_at_freelists.org 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
-----------------------------------------------------------------

Received on Wed Jun 23 2004 - 09:43:47 CDT