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

"David FitzGerald" <david_at_fitzg.com> wrote in message news:7f966bc5.0301220521.104eb3e4_at_posting.google.com...
> Since I posted last, I did what you said and changed the date, removed
> the loop and used 7 inserts and I also disabled all constraints and
> indexes on the table. As a result, things seem to run about 40%
> faster. Wow! Hopefully since the data is being inserted in a good
> order the rebuilding of the index will be fast.

well fast may be a relative term!

> On a sidenote -- if it errors during the insert, is it possible to
> tell Oracle to re-run picking up where it left off?

Oracle will rollback your uncommitted changes, so

If you do no commits until the end then no changes will be made - but undo space required will be huge.
If you commit after each insert you only have to retry the failed inserts. If you commit within a loop then who can tell how far you've got - but it sounds like you aren't doing this.

You could also try doing direct path inserts

insert /*+ APPEND */ into FORECAST. This should bring you speed and resource saving benefits, though it may cost disk space since IIRC it creates new blocks above the HWM for the table.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************