Re: Temporary table use and the redo log
Date: Fri, 11 Sep 2009 18:05:18 +0100
<codefragment_at_googlemail.com> wrote in message
> I'm using a global (session) temporary table to break up a section
> of a stored procedure that is needed in
> a number of places. Always for use in a select.
> If the database crashes, gets rolled back, anything, I don't care
> about the contents of that
> table. Especially -I don't want redo logs for it-. Is there any way I
> can accomplish this?
> From what I can see so far I should look into:
> - unrecoverable
> - insert /*+APPEND */ into temp_table select...
> - possibly using a table variable
> Nologging is on by default for a tempory table but you still get some
> Am I on the right track?
I've read through the posts, and don't see a version number. As various people have suggested, in principle the optimum strategy you want is
create temporary table ... on commit preserve rows;
insert /*+ append */
You still generate undo and redo on indexes, and various feature will invalidate the append hint.
But there are various bugs in different versions of Oracle that cause problems. Most versions of 9i, for example, lose the bulk processing optimisation of insert as select; all versions up to 10.2.0.4 invalidate all cursors on the object when you truncate.
-- Regards Jonathan Lewis http://jonathanlewis.wordpress.com Author: Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.htmlReceived on Fri Sep 11 2009 - 12:05:18 CDT