Optimising PL/SQL Script. Takes 2 days to run.
Date: 21 Jan 2003 05:17:03 -0800
Message-ID: <7f966bc5.0301210517.5d3b54ae_at_posting.google.com>
[Quoted] [Quoted] Firstly, sorry for cross-posting -- I don't know the correct group to post to.
[Quoted] 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:
- Select all LOCATION's and ITEM's from ITEM_LOCATIONS table.
- 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.
[Quoted] 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
[Quoted] 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 - 14:17:03 CET