Re: Temporary table use and the redo log

<codefragment_at_googlemail.com> wrote in message news:fe95ef7a-b4cb-436b-b24e-827177ecf47e_at_37g2000yqm.googlegroups.com...
> Hi
> 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
> logging
>
> Am I on the right track?
>
> thanks

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 */
    commit;
    query
    truncate;

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.html