Home » RDBMS Server » Performance Tuning » Append hint, Truncate, Global Temp Table
Append hint, Truncate, Global Temp Table [message #215802] Tue, 23 January 2007 23:52 Go to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
Is it necessary to truncate the Global temp table, then use Append hint when inserting to it? Let's say it's populated for the first time. I mean do truncate and append hint really help in initially populating a global temp table?? sounds redundant to me.. but i need your confirmation.. thanks in advance..
Re: Append hint, Truncate, Global Temp Table [message #215830 is a reply to message #215802] Wed, 24 January 2007 01:01 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Non-intuitively, the APPEND hint will make for faster inserts into a GTT. I was surprised, but I test it and APPEND is definitely faster because it does not write to REDO. I have no idea why GTT's would even need REDO.

If you suspect you may have used it once already in the session (or since the last commit for ON COMMIT DELETE ROWS) then you can TRUNCATE, it will be quick.

Ross Leishman
Re: Append hint, Truncate, Global Temp Table [message #215933 is a reply to message #215830] Wed, 24 January 2007 05:56 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
GTT does not produce any redo directly.
But produces UNDO which inturn produces REDO. There is a bug in 9i where GTT produces lot more REDO than a regular table!.
Previous Topic: Partitioning with ONline Redefinition
Next Topic: users not logged on last 30 days
Goto Forum:
  


Current Time: Sat Dec 10 11:11:04 CST 2016

Total time taken to generate the page: 0.06542 seconds