Re: Temporary table use and the redo log

From: <>
Date: Thu, 10 Sep 2009 03:15:28 -0700 (PDT)
Message-ID: <>

> > 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
(ii) if you need to delete from the temp table before you begin then:
(iii) add the below at the end of the insert, just before the END
(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

Original text of this message