Re: Temporary table use and the redo log
Date: Thu, 10 Sep 2009 03:15:28 -0700 (PDT)
Message-ID: <f26b3d27-789b-4d0d-af21-df938cd2b3ad_at_g23g2000yqh.googlegroups.com>
> > By the way, who tells you that the inserts are causing the redo > > generation? There might other DML/DDL going on that generates redo.
Good call :-) There was a delete in there which I missed. I now use
EXECUTE IMMEDIATE 'TRUNCATE TABLE TP_TEMP'; and it works, i.e. no redo information being generated.
So summary for future reference of all the steps needed
- you have a stored procedure which has an insert into a temporary table and 2 selects working on that temporary table
(1) split the procedure into 2, one half with the insert, the other
half with the selects which calls the first
(2) in the insert stored proc:
(i) add the below just before the first BEGIN
PRAGMA AUTONOMOUS_TRANSACTION;
(ii) if you need to delete from the temp table before you begin then:
EXECUTE IMMEDIATE 'TRUNCATE TABLE TP_TEMP';
(iii) add the below at the end of the insert, just before the END
COMMIT;
(iv) Change your insert to look like the below
INSERT /*+APPEND */ INTO <your temp table>
Thats it. Now of course it needs proper testing and I can't say if it will increase or slow performance or have any other side effects. Also, its an ugly hack so as a last resort Received on Thu Sep 10 2009 - 05:15:28 CDT