| GLOBAL TEMPORARY TABLE [message #444284] |
Sat, 20 February 2010 02:57  |
 |
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
|
Senior Member |
|
|
What is the best option for GLOBAL TEMPORARY TABLE
1)
option create GLOBAL TEMPORARY TABLE with ON COMMIT DELETE ROWS.
and wheverever this is used for calculation commit at the end of porcedure.
CREATE GLOBAL TEMPORARY TABLE gtt_test
(
A NUMBER
)ON COMMIT DELETE ROWS;
CREATE OR REPLACE PROCEDURE my_proc ( p_in in number)
as
begin
insert into gtt_test (a)
select rownum from dual connect by level < p_in ;
commit;
end;
select * from gtt_test ;
2) create GLOBAL TEMPORARY TABLE without ON COMMIT DELETE ROWS and wheverever this is used use delete from Temp table /Truncate table and then user it.
CREATE GLOBAL TEMPORARY TABLE gtt_test
(
A NUMBER
);
CREATE OR REPLACE PROCEDURE my_proc ( p_in in number)
as
begin
delete from gtt_test ;
insert into gtt_test (a)
select rownum from dual connect by level < p_in;
end;
select * from gtt_test ;
|
|
|
|
|
|
|
|
| Re: GLOBAL TEMPORARY TABLE [message #444290 is a reply to message #444286] |
Sat, 20 February 2010 03:40  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
If one of the options were always better than the other, Oracle would have phased out the other one (ok, in an ideal world).
The difference between PRESERVE ROWS and DELETE ROWS is this:
a GTT can store data that is only visible to a single session. If you store data in it that has to be available from within that session, even after a commit took place, then you use PRESERVE ROWS. If however a commit means you want to start all over with a fresh and empty GTT, use DELETE ROWS.
So, one makes the data persistent for SESSION scope, the other for TRANSACTION.
[Edit:typo]
[Updated on: Sat, 20 February 2010 03:40] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: GLOBAL TEMPORARY TABLE [message #444393 is a reply to message #444349] |
Sun, 21 February 2010 09:25 |
|
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
|
Senior Member |
|
|
Kevin Meade wrote on Sat, 20 February 2010 14:27
Of course, no one has asked... WHY GTT? Why do you think you need global temp tables? What is your reason for using them?
Kevin
While porting code from Sybase/MS Sql server to Oracle this is the Option/alternative We/People look for Temp ( #tables ) in Oracle.
Michel Cadot wrote on Sat, 20 February 2010 14:52
GTT are great when you use them appropriatly but very bad when you use them as Sybase temporary tables.
You might want to suggest good alternative !! ( other then restructure the complete Application as this need long time and this luxury we don't have ) 
[Updated on: Sun, 21 February 2010 09:26] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: GLOBAL TEMPORARY TABLE [message #444402 is a reply to message #444284] |
Sun, 21 February 2010 12:44 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
Some good posts. And to Michel's point, GLOBAL TEMPORARY TABLES are more sophisticated than a simple "real" table so let us review a few points about the GTT. In reviewing these points it should become apparent that the GTT is not something that should be used but rarely because most people do not bother to read documentation or test their usage of special features before employing them.
Perhaps the most interesting thing about the GTT is defined in the first bullet below. Most people do not really know what ON COMMIT PRESERVE ROWS actually means. They usually either never have a problem with it or they find out after the fact when rows they look for are not in the GTT as they expected. Here are what I consider significant features of a GTT.
1) GLOBAL TEMPORARY TABLES are always is some form, temporary. ON COMMIT PRESERVE ROWS is some what misleading. When a commit is issued, rows in a GTT are not TRULY COMMITED in the traditional sense. Temporary means that by design, the data will eventually go away automatically at a predefined moment. A GTT comes in two forms:
a) transaction persistent
b) session persistent
What does really mean? The commit status of a GTT defines the lifetime of rows in the table. Thus DELETE ROWS = rows go away on commit/rollback where as PRESERVE ROWS = rows go away at the end of the session. Therefore, even specifying ON COMMIT PRESERVE ROWS does not commit the rows forever, only for the duration of your session. This can easily be demonstrated with a simple test case, or you can read the manuals for this information.
SQL> create global temporary table kev_gtt1
2 on commit delete rows
3 as
4 select * from dual;
Table created.
SQL> select * from kev_gtt1;
no rows selected
Notice in the first case, that the implied commit as a result of the table create means that rows were deleted from the GTT.
SQL> drop table kev_gtt1;
Table dropped.
SQL> create global temporary table kev_gtt1
2 on commit preserve rows
3 as
4 select * from dual;
Table created.
SQL> select * from kev_gtt1;
D
-
X
1 row selected.
SQL> connect ...
Enter password: *********
Connected.
SQL> select * from kev_gtt1;
no rows selected
In the second case, reconnecting to the same user ends the session and creates a new one. Note how the rows in the GTT were removed (via truncate I believe).
Thus when looking at a GLOBAL TEMPORARY TABLE, you will want to consider how long you want your rows to live before they disappear and indeed even if the temporary nature of the GTT is a needed component of your solution design. If it is not then why are you using a GTT?
2) GLOBAL TEMPORARY TABLES are SESSION ISOLATED (my term not Oracle's). As Michel points out, this has benefits under the covers. But from the perspective of code design, it means that rows is a GTT can only be seen by the session that created them. Most people understand this feature quite readily. Sessions can work knowing that what they put in a GTT will never be changed by nor be seen by other sessions.
Once again, when looking at a a GLOBAL TEMPORARY TABLE, you will want to consider if this feature of the GTT is of value to your solution design. If it is not then why are you using a GTT?
3) GLOBAL TEMPORARY TABLES manage their space differently from "real" tables. When a GTT needs to physically write its rows somewhere, it writes its rows to a segment from the TEMP tablespace. Because a GTT uses TEMP space, there is less REDO generated when using a GTT as vs. a "real" table. However, a GTT fully participates in transaction semantics and as such changing data in a GTT does generate UNDO information and UNDO information IS ALWAYS protected by REDO. Some people have done informal tests on their own (do some googles) that show REDO for a GTT is about half (50%) of normal redo in many situations.
Once again, when looking at a GLOBAL TEMPORARY TABLE, you will want to consider if this space management aids your solution design. If this savings in REDO is not beneficial to your solution design then why are you using a GTT?
There are of course other considerations. GLOBAL TEMPORARY TABLES are an advanced Oracle feature and as one who uses advanced features I can attest that not all advanced features play well together. As just one example of the nuances of GTTs, consider the use of a GTT with PRESERVE ROWS, and a transaction design that employs multiple nested AUTONOMOUS TRANSACTIONS. You may be headed for trouble.
So now that you have read the above, we get back to the real question... WHY GTT? Why do you want to use a GTT in your solution?
Which of the three characteristics of GLOBAL TEMPORARY TABLES is useful to your solution design:
Quote:1) GLOBAL TEMPORARY TABLES are always is some form, temporary.
2) GLOBAL TEMPORARY TABLES are SESSION ISOLATED.
3) GLOBAL TEMPORARY TABLES manage their space differently.
Explain to us which of the above three is necessary and/or beneficial to your solution design? If you cannot, then why use the GTT?
Kevin
[Updated on: Mon, 22 February 2010 07:35] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: GLOBAL TEMPORARY TABLE [message #444585 is a reply to message #444284] |
Mon, 22 February 2010 13:48 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
Just a couple of things to think about. GTT's are fast because they are simply blocks in the SGA or with overflow in the TEMPORARY table-space. They are not truncated so much as the temporary blocks are released. That's why you can't have a storage clause.
You can have indexes on GTT's which are also held in memory or temp space. and are just as isolated as the data.
If you are using GTT's via a web connection, you are probably in trouble. Multiple connections to the web server will use the same session and will tend to cause sharing problems with GTT's. (Session specific data)
|
|
|
|
|
|
|
|
| Re: GLOBAL TEMPORARY TABLE [message #444588 is a reply to message #444284] |
Mon, 22 February 2010 15:24 |
|
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
Thanks Bill, this is nice. A quick recap from Tom's thread gives more detail on the space management of the GTT and gives a hint as to its best use (INSERT/SELECT only).
Quote:If all you do is INSERT and SELECT from the temp tables -- very very little redo is generated. We
do not generate any redo to REDO the insert, but we do generate rollback (undo) information and
this undo is protected by redo.
Hence, if you INSERT into a temp table, a very little amount of UNDO will be generated (in the
event you rollback, we need to undo the insert as well). That UNDO will be protected by REDO. The
INSERT itself will not be.
It is only when you start massively updating or deleting this temp object that large amounts of
redo would be generated. For example, suppose you put 1,000,000 rows into the temp table. That
insert would generate little UNDO and correspondingly little REDO. Now, suppose you "delete from
temp_table" -- that will generate a massive amount of UNDO (in order to rollback the delete if you
need) and that UNDO will be protected by REDO.
Since temporary tables are used almost 100% for INSERTS and SELECTS, this is just fine. You should
rely on a COMMIT, TRUNCATE or end of SESSION to "delete" the data and that will have no effect on
the logs since the data will just be "wiped out" -- not logged.
and also
Quote:gtt's generate undo and everything, they do the same sort of consistent read stuff "real" tables
do.
they are cacheable.
I am taking Bill at his commentary that the GTT is cacheable in the SGA and indeed will be cached until it is necessary to write actual temp space to disk (which could be never). Thus it is possible that small numbers of rows in a GTT may never cause physical I/O and hence offer an opportunity for great speed in the appropriate situations.
Thanks again Bill for just the right link. Hat off to Tom Kyte again. Kevin
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
