> 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
No, it doesn't make sense, really. The redo generated for temporary
tables is minimal, and can't be disabled. Thus a direct-path insert
insert.
"tea" <t
@mailinator.com> wrote in message
...
> 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
-----------------------------------------------Reply-----------------------------------------------
On May 7, 2:11 pm, "fitzjarr
@cox.net" <fitzjarr
@cox.net> wrote:
> On May 7, 2:30 pm, "tea" <t
@mailinator.com> wrote:
> > 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
> No, it doesn't make sense, really. The redo generated for temporary
> tables is minimal, and can't be disabled. Thus a direct-path insert
> behaves no differently for a temporary table than does a plain-vanilla
> insert.
David,
I agree from the redo aspect. But:
- Are there no benefits from not needing to search for free space?
- Or from building a complete (previously empty) block in memory
rather than updating an existing block?
Just curious
--
Hans Forbrich (mailto: Fuzzy.GreyBeard_at_gmail.com)
*** Feel free to correct me when I'm wrong!
*** Top posting [replies] guarantees I won't respond.
Add to del.icio.us |
Digg this |
Stumble it |
Powered by Megasolutions Inc
