Howto replace temp-tables with virtual ones?
Date: 4 Jun 2002 14:52:19 -0500
Message-ID: <qf6qfuojeqoc6mo6oj8lnsa0mbkb1rb9oe_at_news.nym-alias.net>
Hi,
I would like to change my procedures from using temp tables to virtual
ones.
Right now the procedure is working like this:
- A select statement with 6-10 joins reads the data in a temp table
- 1 to many cursors cycle through this temp table adding information mostly from 1->n relations
- The whole temp table is passed to crystal report and deleted.
Here's my test example.
CREATE OR REPLACE PROCEDURE coll_emp4 IS
begin
delete from tora_coll_emp;
insert into tora_coll_emp (job_id,members) select job_id,'N/A' from
jobs;
declare cursor c1 is
select e.last_name,t.job_id from employees e,tora_coll_emp t
where t.job_id=e.job_id
for update of members ;
begin
for m_rec in c1 loop
update tora_coll_emp set members=decode(members,'N/A',m_rec.last_name,members||', '||m_rec.last_name) where current of c1;
end loop;
end;
commit;
end coll_emp4;
Now would i use a record or a index by table or temporary tables for this purpose? What is fastest to do everything in RAM and not write to disk?
How do I fill such a table from a select statement?
How do I add or reserve and pre fill the necessary columns for the updates required afterwards?
Any help would be greatly appreciated.
thanks
ken Received on Tue Jun 04 2002 - 21:52:19 CEST