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

From: Jim Kennedy <kennedy-down_with_spammers_at_attbi.com>
Date: Tue, 21 Jan 2003 15:45:58 GMT
Message-ID: <WWdX9.79038$1q3.11932_at_sccrnsc01>

Do it in sql and do it from seperate sessions. Also if you have it set up in the dbv use parellel query.(if you have multiple cpus and a lot of disks. session 1

             insert into FORECAST (item,location, startdate,other column)
                (select ITEM, LOCATION,to_date(

'23-Jan-2003','dd-mmm-yyyy')+((rownum-1)*7) from ITEM_LOCATIONS order by item,location);

session 2

             insert into FORECAST (item,location, startdate,other column)
                (select ITEM, LOCATION,to_date(

'23-Jan-2003','dd-mmm-yyyy')+((rownum)*7) from ITEM_LOCATIONS order by item,location);

session 3

             insert into FORECAST (item,location, startdate,other column)
                (select ITEM, LOCATION,to_date(

'23-Jan-2003','dd-mmm-yyyy')+((rownum+1)*7) from ITEM_LOCATIONS order by item,location);
session 4

             insert into FORECAST (item,location, startdate,other column)
                (select ITEM, LOCATION,to_date(

'23-Jan-2003','dd-mmm-yyyy')+((rownum+2)*7) from ITEM_LOCATIONS order by item,location);
session 5

             insert into FORECAST (item,location, startdate,other column)
                (select ITEM, LOCATION,to_date(

'23-Jan-2003','dd-mmm-yyyy')+((rownum+3)*7) from ITEM_LOCATIONS order by item,location);
session 6

             insert into FORECAST (item,location, startdate,other column)
                (select ITEM, LOCATION,to_date(

'23-Jan-2003','dd-mmm-yyyy')+((rownum+4)*7) from ITEM_LOCATIONS order by item,location);
session 7

             insert into FORECAST (item,location, startdate,other column)
                (select ITEM, LOCATION,to_date(

'23-Jan-2003','dd-mmm-yyyy')+((rownum+5)*7) from ITEM_LOCATIONS order by item,location);

Then you have seven sessions doing the sql all at once. The other posters are correct in that we don;t know enough about your system to speed it up. Jim

--
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family.  Remove the negative part, keep the minus sign.  You can figure
it out.
"David FitzGerald" <david_at_fitzg.com> wrote in message
news:7f966bc5.0301210517.5d3b54ae_at_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 - 16:45:58 CET