Home » SQL & PL/SQL » SQL & PL/SQL » Global Temporary table storage (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit)
Global Temporary table storage [message #660323] Tue, 14 February 2017 22:17 Go to next message
abhi_orcl
Messages: 39
Registered: December 2016
Member
Hello Everyone,

I have a requirement where I have a global temp table created with on commit preserve rows option. Now, this table will hold reporting data and the application which uses it might be triggered by hundreds of users simultaneously.Basically the application inserts some data from multiple source tables using joins and after into the temp table and after applyig multiple validations for each session depending upon the user input, the report is generated using the value in the temp table at that time.
I want to know the actual storage of the temp tables- Consider the case when each user simultaneously inserts 10k rows into the temp table and we have total 100 users. Though each user will see only the data relevant to his/her session, is it true that ultimately there would be an overhead of selecting 10k rows from a total of 1 million(10000 * 100) at any point of time for any user?
Re: Global Temporary table storage [message #660324 is a reply to message #660323] Tue, 14 February 2017 22:51 Go to previous messageGo to next message
BlackSwan
Messages: 25858
Registered: January 2009
Location: SoCal
Senior Member
abhi_orcl wrote on Tue, 14 February 2017 20:17
Hello Everyone,

I have a requirement where I have a global temp table created with on commit preserve rows option. Now, this table will hold reporting data and the application which uses it might be triggered by hundreds of users simultaneously.Basically the application inserts some data from multiple source tables using joins and after into the temp table and after applyig multiple validations for each session depending upon the user input, the report is generated using the value in the temp table at that time.
I want to know the actual storage of the temp tables- Consider the case when each user simultaneously inserts 10k rows into the temp table and we have total 100 users. Though each user will see only the data relevant to his/her session, is it true that ultimately there would be an overhead of selecting 10k rows from a total of 1 million(10000 * 100) at any point of time for any user?
Yes, but what difference does it make?
It appears that you suffer from Compulsive Tuning Disorder.
Re: Global Temporary table storage [message #660328 is a reply to message #660323] Wed, 15 February 2017 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 65388
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
is it true that ultimately there would be an overhead of selecting 10k rows from a total of 1 million(10000 * 100) at any point of time for any user?
Wrong, each user selects/sees his rows and only these ones.
Each user temp table is in a different storage space.

Re: Global Temporary table storage [message #660359 is a reply to message #660328] Wed, 15 February 2017 05:48 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2710
Registered: January 2010
Location: Connecticut, USA
Senior Member
Hi Michel,

I know you meant each session selects/sees own rows and only these ones.

SY.
Re: Global Temporary table storage [message #660360 is a reply to message #660359] Wed, 15 February 2017 05:53 Go to previous messageGo to next message
Michel Cadot
Messages: 65388
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes, indeed, thank for the correction.

Re: Global Temporary table storage [message #660366 is a reply to message #660360] Wed, 15 February 2017 07:26 Go to previous messageGo to next message
Bill B
Messages: 1718
Registered: December 2004
Senior Member
The definition of the GTT is stored in the database but the inserted data is stored in the temporary tablespace for the user. Another thing you can do with a GTT is define indexes which work just as will as indexes on permanent tables.
Re: Global Temporary table storage [message #660386 is a reply to message #660366] Wed, 15 February 2017 10:30 Go to previous messageGo to next message
abhi_orcl
Messages: 39
Registered: December 2016
Member
OK.Thanks all for the inputs but I am bit confused now. Apologies for not being so elaborate before.I have a temp table say t_temp in schema t_user. t_user default temporary tablespace is temp.
Now, when I create the temp table it would be created on the temporary tablespace temp. I create a procedure with owner as t_user which does the business related DMLs in the table t_temp. Now, this procedure would be called by hundreds of users in different sessions concurrently.
So can I assume that still the table won't actually hold 1 million records at any point of time; Basically the data related to the session would have to be fetched from a table holding 1 million records. Or is it something like the for each session, the temp table would be created on the fly and would always hold the data specific to that session only and not the cumulative data for all sessions.

Regards
Re: Global Temporary table storage [message #660387 is a reply to message #660386] Wed, 15 February 2017 10:34 Go to previous messageGo to next message
Michel Cadot
Messages: 65388
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Or is it something like the for each session, the temp table would be created on the fly and would always hold the data specific to that session only and not the cumulative data for all sessions.
This is that.
For each session, Oracle creates a temporary segment in the user temporary tablespace holding the data of the session and only of this session.

Re: Global Temporary table storage [message #660388 is a reply to message #660386] Wed, 15 February 2017 10:35 Go to previous messageGo to next message
Bill B
Messages: 1718
Registered: December 2004
Senior Member
The second. The only thing stored in the database is the structure of the Global Temp table (GTT) and any indexes or constraints that it has. When you insert/update/select from the GTT you are accessing a data structure setup in your temporary tablespace that contains only the data associated with your session. The hundreds of separate sessions all have their own table structure in the temporary tablespace. When the session disconnects the structure is released.
Re: Global Temporary table storage [message #660389 is a reply to message #660387] Wed, 15 February 2017 10:36 Go to previous message
abhi_orcl
Messages: 39
Registered: December 2016
Member
Great!! As always..thank you Michel,BlackSwan and Bill..This is indeed a great forum

[Updated on: Wed, 15 February 2017 10:36]

Report message to a moderator

Previous Topic: Problem while using AUTHID CURRENT_USER
Next Topic: Oracle BLOB DATA
Goto Forum:
  


Current Time: Fri Feb 23 09:05:47 CST 2018

Total time taken to generate the page: 0.35690 seconds