Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Optimising PL/SQL Script. Takes 2 days to run.
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);
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);
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);
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);
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...Received on Tue Jan 21 2003 - 09:45:58 CST
> 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;