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.
I don't know if this approach would work for you or not
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Jan 21 13:31:23 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> create table item_locations(item number,location number);
Table created.
SQL> begin
2 for i in 1..1000 loop
3 for j in 1..65 loop
4 insert into item_locations values(i,j);
5 end loop;
6 end loop;
7 commit;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> select count(*)
2* from item_locations;
COUNT(*)
65000
SQL> create table forecast(item number,location number,forecast_date date);
Table created.
SQL> get s:\davef
1 DECLARE
2 cursor c_items is 3 select ITEM, LOCATION from ITEM_LOCATIONS; 4 start_date DATE ; 5 BEGIN 6 start_date := '23-Jan-2003'; 7 for this_item in c_items LOOP 8 for ctr in 0..6 LOOP 9 insert into FORECAST 10 values(this_item.item, this_item.location, start_date+ctr); 11 END LOOP; 12 END LOOP; 13 commit;
PL/SQL procedure successfully completed.
Elapsed: 00:01:42.07
SQL> select count(*) from forecast;
COUNT(*)
455000
Elapsed: 00:00:02.02
SQL> truncate table forecast
2 ;
Table truncated.
Elapsed: 00:00:05.01
SQL> @s:\nialll
65000 rows created.
Elapsed: 00:00:00.07
65000 rows created.
Elapsed: 00:00:00.09
65000 rows created.
Elapsed: 00:00:00.06
65000 rows created.
Elapsed: 00:00:00.05
65000 rows created.
Elapsed: 00:00:00.07
65000 rows created.
Elapsed: 00:00:00.06
65000 rows created.
Elapsed: 00:00:00.07
SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
SQL> get s:\nialll.sql
1 insert into FORECAST select item, location,
to_date('23-jan-03','dd-mon-yyy
y') from item_locations;
2 insert into FORECAST select item, location,
to_date('23-jan-03','dd-mon-yyy
y') +1 from item_locations;
3 insert into FORECAST select item, location,
to_date('23-jan-03','dd-mon-yyy
y') +2 from item_locations;
4 insert into FORECAST select item, location,
to_date('23-jan-03','dd-mon-yyy
y') +3 from item_locations;
5 insert into FORECAST select item, location,
to_date('23-jan-03','dd-mon-yyy
y') +4 from item_locations;
6 insert into FORECAST select item, location,
to_date('23-jan-03','dd-mon-yyy
y') +5 from item_locations;
7* insert into FORECAST select item, location,
to_date('23-jan-03','dd-mon-yyy
y') +6 from item_locations;
8
I.E. Do 7 different inserts.
Obviously you could wrap this approach into a PL?SQL block as well. HTH
-- Niall Litchfield Oracle DBA Audit Commission UK ***************************************** Please include version and platform and SQL where applicable It makes life easier and increases the likelihood of a good answer ******************************************Received on Tue Jan 21 2003 - 08:06:52 CST