Home » SQL & PL/SQL » SQL & PL/SQL » Difference in DML operations on Table and Global temp table
Difference in DML operations on Table and Global temp table [message #411014] Wed, 01 July 2009 08:32 Go to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

Could there be any difference in performance on Normal table and Global temp table in respect to DML operations.

Example;


Inserting 6 M records.
Insert into table..........

same no of records
Insert into Global_temp_table

Thanks in advance
Re: Difference in DML operations on Table and Global temp table [message #411023 is a reply to message #411014] Wed, 01 July 2009 08:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, the most likely it might be.

Regards
Michel
Re: Difference in DML operations on Table and Global temp table [message #411039 is a reply to message #411014] Wed, 01 July 2009 11:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I would expect the GTT to be faster, as it doesn't have to write nearly as much redo information.
Re: Difference in DML operations on Table and Global temp table [message #411041 is a reply to message #411039] Wed, 01 July 2009 12:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
...and it bypasses buffer cache.

Regards
Michel
Re: Difference in DML operations on Table and Global temp table [message #411267 is a reply to message #411041] Thu, 02 July 2009 22:47 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I wonder if there are situations where using a GTT would actually be slower?

Kevin
Re: Difference in DML operations on Table and Global temp table [message #411350 is a reply to message #411041] Fri, 03 July 2009 03:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
...and it bypasses buffer cache.

Does it?
I've never heard that - do you have any more details?
Re: Difference in DML operations on Table and Global temp table [message #411496 is a reply to message #411267] Sat, 04 July 2009 06:02 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Kevin Meade wrote on Thu, 02 July 2009 22:47
I wonder if there are situations where using a GTT would actually be slower?

Kevin


Maybe disk or freespace contention within the TEMP tablespace?

Interesting question; my assumption of course is that GTT is faster, at worst, but that there are relatively few situations in which it can be used.

Re: Difference in DML operations on Table and Global temp table [message #411505 is a reply to message #411350] Sat, 04 July 2009 07:41 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
JRowbottom wrote on Fri, 03 July 2009 18:12
Quote:
...and it bypasses buffer cache.

Does it?
I've never heard that - do you have any more details?

I don't have any actual facts, just speculation and wild assumptions - but logically what COULD it do with the buffer cache?

The Buffer Cache contains SHARED blocks read from disk and blocks to be written back to disk. Data in a GTT is not shareable; although it may be written to disk, it is only done as part of memory management - not permanent storage.

Or - to be more precise - the Buffer Cache is part of the SGA - GTT data is part of the PGA.

Ross Leishman
Re: Difference in DML operations on Table and Global temp table [message #411522 is a reply to message #411505] Sat, 04 July 2009 10:23 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is exactly what I was thinking but searching for an evidence I didn't find any.

Regards
Michel
Previous Topic: ORA-06550,PLS-00201 error
Next Topic: rebuilding indexes / dbms_stats.gather_schema_stats (merged 5) 10.1.0.5 unix
Goto Forum:
  


Current Time: Thu Dec 08 23:47:55 CST 2016

Total time taken to generate the page: 0.07107 seconds