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

Maybe it's a really stupid question, I don't know. But: do you really need to modify this data, or you just want to read it?

Is so, then you can just make a view with the suggestion of Pablo Sanchez.

For example:

SQL> create table item_locations (item char(1), location number(1));

Table created.

SQL> create table date_ref (d date);

Table created.

SQL> create table pivot (n number(1));

Table created.

SQL> insert into date_ref values(to_date('07-APR-03'));

1 row created.

SQL> begin
  2 for m in 0..6 loop
  3 insert into pivot values(m);
  4 end loop;
  5 end;
  6 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> create view item_dated as
  2 select i.*, d.d + p.n date_ref from item_locations i, pivot p, date_ref d;

View created.

SQL> insert into item_locations values ('A', 0);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from item_dated;

ITEM LOCATION DATE_REF

---- ---------- ---------
A             0 07-APR-03
A             0 08-APR-03
A             0 09-APR-03
A             0 10-APR-03
A             0 11-APR-03
A             0 12-APR-03
A             0 13-APR-03

7 rows selected.

Then, if you want to modify this data, you do:

SQL> create table item_dated_stock as
  2 select * from item_dated;

Table created.

HTH Kamal Received on Fri Jan 24 2003 - 16:24:30 CET