Re: PL/SQL SP is slow - 1.2 Million Records - Help me

On Wed, 20 Oct 1999 20:31:19 GMT, v_sreedhar_at_usa.net wrote:

>PL/SQL SP is slow - 1.2 Million Records - Help me
>Hi Guys,
>
>i have three tables,
> prod_old, prod_curr, prod_to_be_approved.
>
>both prod_old & prod_curr is having around 1.2 Million Records.
>
>System configuration ( LIVE SERVER ) : 4 GB RAM, Processors : 4
>Space : 100 GB
>
>i am loading the data in the prod_curr into a cursor
>and try to compare the prod_old,
>if the data is not in prod_old then
> Insert a row into the prod_to_be_approved
>Else
> compare around 12 columns of data
> of prod_old with data of prod_curr
> if any data changed then
> insert a row into the prod_to_be_approved
> else
> DONT PROCESS . continue the loop.
>end if
>end if
>
>if i select say around 100,000 rows,
>it is taking approximately 16 - 28 minutes. (max 35 minutes )when i
>tried to
>select * from prod_curr into a cursor
>that is it, it took two and half days
>and still it was running, so we killed the process.
>PLEASE HELP ME.
>
>Sreedhar V
>sreedharv_at_bigwords.com
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

This is a typical case of something that shouldn't be done in PL/SQL (in Oracle 7 there are no array fetches in PL/SQL) and which should be done preferably in sql by using

insert into prod_to_be_approved
select * from prod_curr
where not exists
(select 'x'
 from prod_old
 where prod_old.primary_key = prod_curr.primary_key)

and

insert into prod_to_be_approved
select * from prod_curr
where exists
(select 'x'
 from prod_old
 where prod_old.primary_key = prod_curr.primary_key  and prod_old.col1 <> prod_curr.col2
-- and so on
)

Presto, no 1.2 million fetches (which is killing performance)

Hth,

Sybrand Bakker, Oracle DBA

Hth,

Sybrand Bakker, Oracle DBA Received on Wed Oct 20 1999 - 16:44:57 CDT