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

I don't know if this approach would work for you or not

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Jan 21 13:31:23 2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production

SQL> create table item_locations(item number,location number);

Table created.

SQL> begin
  2 for i in 1..1000 loop
  3 for j in 1..65 loop
  4 insert into item_locations values(i,j);   5 end loop;
  6 end loop;
  7 commit;
  8 end;
  9 /

PL/SQL procedure successfully completed.

SQL> select count(*)
  2* from item_locations;

  COUNT(*)

     65000

SQL> create table forecast(item number,location number,forecast_date date);

Table created.

SQL> get s:\davef
  1 DECLARE

  2      cursor c_items is
  3      select ITEM, LOCATION from ITEM_LOCATIONS;
  4      start_date DATE ;
  5   BEGIN
  6      start_date := '23-Jan-2003';
  7      for this_item in c_items LOOP
  8            for ctr in 0..6 LOOP
  9               insert into FORECAST
 10               values(this_item.item, this_item.location,
start_date+ctr);
 11            END LOOP;
 12      END LOOP;
 13      commit;

PL/SQL procedure successfully completed.

Elapsed: 00:01:42.07
SQL> select count(*) from forecast;

  COUNT(*)

    455000

Elapsed: 00:00:02.02
SQL> truncate table forecast
  2 ;

Table truncated.

Elapsed: 00:00:05.01
SQL> _at_s:\nialll

65000 rows created.

Elapsed: 00:00:00.07

65000 rows created.

Elapsed: 00:00:00.09

65000 rows created.

Elapsed: 00:00:00.06

65000 rows created.

Elapsed: 00:00:00.05

65000 rows created.

Elapsed: 00:00:00.07

65000 rows created.

Elapsed: 00:00:00.06

65000 rows created.

Elapsed: 00:00:00.07
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> get s:\nialll.sql
  1 insert into FORECAST select item, location, to_date('23-jan-03','dd-mon-yyy
y') from item_locations;
  2 insert into FORECAST select item, location, to_date('23-jan-03','dd-mon-yyy
y') +1 from item_locations;
  3 insert into FORECAST select item, location, to_date('23-jan-03','dd-mon-yyy
y') +2 from item_locations;
  4 insert into FORECAST select item, location, to_date('23-jan-03','dd-mon-yyy
y') +3 from item_locations;
  5 insert into FORECAST select item, location, to_date('23-jan-03','dd-mon-yyy
y') +4 from item_locations;
  6 insert into FORECAST select item, location, to_date('23-jan-03','dd-mon-yyy
y') +5 from item_locations;
  7* insert into FORECAST select item, location, to_date('23-jan-03','dd-mon-yyy
y') +6 from item_locations;
  8

I.E. Do 7 different inserts.

Obviously you could wrap this approach into a PL?SQL block as well. HTH

--
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

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