Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Optimising PL/SQL Script. Takes 2 days to run.

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@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 - 09:45:58 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US