Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL SP is slow - 1.2 Million Records - Help me
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