Re: Direct-Path on Global_Temp_T ?

"tea" <tea_at_mailinator.com> wrote in message news:133uvisjnk44r3c_at_corp.supernews.com...
> 10gR2/Solaris 8
>
> Does it make any sense/difference to do Direct-Path Insert on GTT
> (Global_Temp_Table) ?
>
> i.e. add "/*+ APPEND */" to
> "INSERT into GTT SELECT * FROM sometable"
>
> or is it moot ?
>
> thanks
>

Pros and Cons:

The table has to be "on commit preserve rows" as you have to commit before you can use the data from an insert /*+ append */ - this means you have to truncate (or end the session) to clear the table. Truncating has undesirable side-effects on popular GTTs until 10.2.

You do get some benefit, though, as the /*+ append */ eliminates the undo generation on the insert, and there will be redo for the undo. But the saving may not be significant, especially if the table is indexed.

So "moot" is a relevant expression. You may get some benefit, but it depends on the circumstances.

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