Sunday, October 21, 2007

Global Temporary Tables

I listened intently to the new Oracle programmer as he described all the struggles he's been having on his first big project. As I've done many times already in his short career, I interrupt with some words of wisdom.

"It's time to add Global Temporary Tables to your toolbelt."

"What are those?" he asks, as he opens the directory with the Oracle documentation. I smile. He has already learned where I always send him first.

"They're the ultimate work tables," I continue. "They're permanent tables, where you can add and modify session-specific data without affecting other sessions."

"What's so special about that?" he asks. "Even with regular tables, you can add and modify the data all you want without affecting other sessions. Just don't commit, and remember to rollback when your session is done."

"Oh yeah? And what about all the persistent work you're doing in your session? How do you commit that?"

"Oh yeah. Does it allow for indexes, and triggers, and views with regular tables?"

"Yep, all of that. See for yourself, it's easy. You've got the manual in front of you, so you drive."

Then I watched as he opened one session, and created a global temporary table.

SQL> CREATE GLOBAL TEMPORARY TABLE worktable (x NUMBER(3));

Table created.


He opened another session and was pleased to see the table there. He then added a row in the first session, committed it, and was planning to use the other session to see if the data was there. But instead he was in for a little surprise.

SQL> INSERT INTO worktable (x) VALUES (1);

1 row created.

SQL> SELECT * FROM worktable;

X
----------
1

SQL> commit;

Commit complete.

SQL> SELECT * FROM worktable;

no rows selected


"Hey!" he shouted. Heads popped up in nearby cubicles. "Where did it go?"

"Keep reading," I said, gesturing towards the "ON COMMIT" options for Global Temporary Tables. "By default, every time you commit your data, it is assumed that you want to clear out your work tables. Try PRESERVE."

He dropped the table, and tried again.

SQL> CREATE GLOBAL TEMPORARY TABLE worktable
2 (x NUMBER(3))
3 ON COMMIT PRESERVE ROWS;

Table created.

SQL> INSERT INTO worktable (x) VALUES (1);

1 row created.

SQL> SELECT * FROM worktable;

X
----------
1

SQL> commit;

Commit complete.

SQL> SELECT * FROM worktable;

X
----------
1


"That's better," he smiled. Now let's check the other session.

SQL> SELECT * FROM worktable;

no rows selected


"Excellent. So this data will remain there until my session ends?"

"Yep. Try it." He exited his session, logged back in, and sure enough the data was gone. "This is great. But what if I want to get rid of the data at some point in my session?"

"Truncate. Truncating the work table will only truncate the data in your session, not all the data."

"Hey neat. Thanks, this will be very useful. What are you doing for lunch later?"

"Aren't you even going to try it? What, you're just going to trust me?" I said. I think he was a little surprised that I would I'd rather talk about work tables than lunch. Frankly, so am I. Thankfully, he worked quickly, typing first in his second session.

SQL> INSERT INTO worktable (x) VALUES (2)

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT * FROM worktable;

X
----------
2

SQL> TRUNCATE TABLE worktable;

Table truncated.

SQL> SELECT * FROM worktable;

no rows selected

SQL> commit;

Commit complete.


"I don't think you need all those commits," I laughed. "But ok, now look back in your first session. If the row you added previously isn't there, then I'm a big fat liar. Otherwise, we can talk about lunch."

SQL> SELECT * FROM worktable;

X
----------
1


"So how about sushi?" he asked.

"Sushi?" I groaned. "Haven't you learned anything from me?"

Comments:
I like that style. Thanks Robert.
 
Master and the apprentice

Thank you for coming back with your style of making excellent reading.
 
This comment has been removed by a blog administrator.
 
This is the best style that an article can have...Greattt!!! Please do post more in this style when time permits. Thanks a lottt!!!!
 
Awesome post! Please do sahre ur knowledge thru ur posts!

Looking forward for more!

~ Siri
 
Hey Robert,

I take many PL/SQL Classes as & when I get time & I must admit that this is one of the best way of expression I have ever come across on Oracle DB related blogs. I will always remember this post & would certaily direct my students to your blog. Keep such posts coming in & Gd Luck :)

Cheers,
Chinmayz
 
This is very informative, your blog are one of the best blog about oracle. I need to book mark this one.
keep on posting, and I will keep reading
Mit
 
I too like the fun style. But more importantly this is the first page I've found to really explain clearly what GTTs are intended for and how you might use them.

But why is it called 'Temporary Table', when in fact it is a permanent table, but intended for storage of temporary DATA? I found this VERY confusing and it took me a good while to understand that I was wrong in thinking that:
you create the table at the point when you need it, eg within a PL/SQL proedure, and the system auto drops the table at the end of your session/transaction.
No matter how many pages I read that DO say that the table "has a persistent definition but data is not persistent" I nevertheless didn't grasp this because I was thinking that the table is temporary in nature. This confusion turned a fairly simple concept into a real headbanger for me.

But thanks for this article Robert, I've got it now.
 
Further to the above I meant to add that my confusion wasn't helped by what seems to be a strange habit out there of introducing your audience to the concept of temporary tables but then just using ordinary tables. Am I missing something? eg see here (and this is not the only example): http://www.dba-oracle.com/t_tuning_global_temporary_tables.htm
Here he talks about using temporary tables and global temporary tables, but in the end he just uses normal tables. For a reader new to the concept of temporary tables (ie his intended audiance), that's just plain confusing.
Or have am i misunderstanding all of this?
 
Excellent style. I remembered the
way of teaching in gr8 O'reilly books. :)


 
Hi

I had a question on how to do debugging on global temporary tables. As i am not able to see the data after i run the session is a way i can debug / see data when i run them.

Thanks
-Pradip
 
Hi,

Similar question as Pradip's (I'm hoping someone has the answer)...

Is there anyway to spy on other sessions global temporary tables/sessions for debugging purposes? I'm using Oracle 11.2.0.1.

Usually I drop and recreate the table as a normal table, but that can be a shlep especially when I'm at the end of a long running operation or... forget which ones I've modified.

I haven't been able to find anything on Google.

Regards,
Dono
 
I think the only way to see what data is going into a temporary table without recreating it is to add a trigger on the temporary work table that inserts the rows into a non-temporary copy of the work table using PRAGMA AUTONOMOUS_TRANSACTION to COMMIT the data without affecting the current session. When you're done, drop the trigger and copy table.
 
True blogger ... nice post!

Even a layman can understand.

Thanks for the post.
 
it helped lot.
pavan.
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?