Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL SP is slow - 1.2 Million Records - Help me

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

From: Andrew Filmer <filmeraj_at_global.co.za.spam>
Date: Fri, 22 Oct 1999 20:06:00 +0200
Message-ID: <7uss9h$g8j$1@ctb-nnrp2.saix.net>


Try using a cursor with minus to compare the difference between the two tables :

   select xyz from prod_curr
   minus
   select xyx from prod_old

and then handle any differences found. This will do a full table scan on both tables, loading the whole lot into memory (looks enough), compare them, and produce a result set (hopefully small), and then you can do cross-checks on whether they're different, or not in prod_curr.

Regards

Andrew

<v_sreedhar_at_usa.net> wrote in message news:7ul8qo$n5n$1_at_nnrp1.deja.com...
> 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.
Received on Fri Oct 22 1999 - 13:06:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US