Re: Optimising PL/SQL Script. Takes 2 days to run.

From: DA Morgan <damorgan_at_exesolutions.com>
Date: Mon, 20 Jan 2003 18:37:23 -0800
Message-ID: <3E2CB263.8814A730_at_exesolutions.com>


Rauf Sarwar wrote:

> david_at_fitzg.com (David FitzGerald) wrote in message news:<7f966bc5.0301220521.104eb3e4_at_posting.google.com>...
> >
> > On a sidenote -- if it errors during the insert, is it possible to
> > tell Oracle to re-run picking up where it left off?
>
> You can have exception handling inside your inner loop (because that
> is where you are doing inserts). The exception handling may not do
> anything but it will atleast keep the loop from breaking and will
> simply go to next record in the loop. This is usefull if you insert
> 200000 rows and routine breaks on one error and you have to start
> again.
>
> Secondly, you can have a counter in your outer loop to do a commit
> after every e.g. 600 rows.
>
> DECLARE
> cursor c_items is
> select ITEM, LOCATION from ITEM_LOCATIONS;
> start_date DATE;
> counter_ NUMBER := 1;
> BEGIN
> start_date := '23-Jan-2003';
> FOR this_item in c_items LOOP
> --
> FOR ctr in 0..6 LOOP
> INSERT INTO FORECAST
> VALUES (this_item.item, item.location, start_date + ctr,
> '0');
> BEGIN
> EXCEPTION
> WHERE OTHERS THEN
> NULL;
> END;
> END LOOP;
> --
> counter_ := counter_ + 1;
> -- 100 * 6 inner loops is 600
> IF (counter_ = 100) THEN
> commit;
> counter_ := 1;
> ENDIF
> END LOOP;
> commit;
> END;
> /
>
> You can modify this according to what others have suggested like no
> inner loop. In that case move the exception to outer loop.
>
> Regards
> /Rauf Sarwar

[Quoted] Just a few quick comments.

I agree with error handling inside a loop but it should not just ignore errors ... it should log them

[Quoted] I disagree with your suggestion about incremental commits. Can't think a better way to kill performance and generate ORA-01555.

Daniel Morgan Received on Tue Jan 21 2003 - 03:37:23 CET

Original text of this message