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

(Also new to PL/SQL).

Surely a standard SQL statement would be more efficient, (I'm a little unsure about the '23-Jan-2003' + ctr)

I would recommend putting some checking into the system so the loop starts at the last day created previously, if there is a failure, the job will be part complete, but the created rows would be stored.

i.e.:

DECLARE
   start_date DATE;
   ctr INT;

BEGIN FOR ctr in 0..6
LOOP
   INSERT INTO FORECAST
   SELECT ITEM, LOCATION, '23-Jan-2003' + ctr, '0'    FROM ITEM_LOCATIONS;    COMMIT;
END LOOP; EXCEPTION END; Cheers, Paul

David FitzGerald wrote:
> Firstly, sorry for cross-posting -- I don't know the correct group to
> post to.
>
> I've written a PL/SQL script to create stock forecast data from
> another table.
>
> The ITEM_LOCATIONS table has approximately 65 million rows in it, with
> about 100,000 unique ITEM's and 650 LOC's.
>
> What I am trying to do is this:
>
> 1) Select all LOCATION's and ITEM's from ITEM_LOCATIONS table.
> 2) For each item from (1) create 7 records in new table (FORECAST),
> each of which has the same data except for a different date
> (sequential) for each one.
>
> The problem is that this is taking an awfully long time to process
> (more than 2 days). I am new to PL/SQL so don't know if I am
> approaching this the wrong way, or if this is the quickest I'll ever
> create over half a billion rows? Any pointers would be massively
> appreciated.
>
> Thanks!
> David
>
> ----------------
>
> DECLARE
> cursor c_items is
> select ITEM, LOCATION from ITEM_LOCATIONS;
>
> start_date DATE ;
>
> 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');
> END LOOP;
> END LOOP;
>
> commit;
> END;
Received on Tue Jan 21 2003 - 15:53:46 CET