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

From: David FitzGerald <david_at_fitzg.com>
Date: 21 Jan 2003 05:17:03 -0800
Message-ID: <7f966bc5.0301210517.5d3b54ae@posting.google.com>

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 - 07:17:03 CST