Skip to Main Content
  • Questions

  • Connection pools and global temporary tables

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Pratap.

Asked: July 07, 2002 - 11:34 pm UTC

Last updated: April 29, 2004 - 2:50 pm UTC

Version: 9.0.0

Viewed 10K+ times! This question is

You Asked

Hi Tom

This is how I have implemented the search functionality on a web application -
(Application server is WebLogic)

The functionality is such that if the numer of rows fetched exceeds a threshold then
an error is to be displayed to the user.

So I do the following steps -

1. insert into a global temporary table.... select ..where rownum <= threshold + 1
2. Get sql%rowcount
3. If count > threshold then I pass an out parameter with the error message
4. If count <= threshold then I open a ref cursor from the global temporary table and pass it as an out parameter

I am facing a problem -

The weblogic application has a connection pool. So data in the temporary table is not being deleted when the connection is closed.
I have to delete the data from the temporary table before inserting into it. This I feel is an overhead and will cause a problem if many users get the same connection from the pool.

The Java guys say that the same connection would never be given to two different clients

My questions are -

1. Is this true that the same connection would never be given to different clients

2. Is there any better way of impelmenting this functionality. I can only think of using analytic function -
count(*) over()
The java guys can then open the result set and after reading the first row decide whether to display the
list or not.


Thanks and regards

Pratap


and Tom said...

Why you would give an error for a search is beyond me -- I would just limit the hits to threshold and tell them (if they have the PATIENCE to page down to "threshold") that hey "your search returned lots of stuff, there might be more but we are only going to show you the first N hits".

That way -- no temp table, no muss, no fuss, end user is happy, you are happy and they'll NEVER have the patience to page down to the end anyway (so they'll never see this)

Ask the "java guys" this: If the same connection is never given to two different clients -- what is the point of the connection pool? You see, the entire POINT of a connection pool is to share the connection between clients (else, you might just as well have each client get their own connection right). Now, maybe what the java guys mean is that "we grab the connection and keep for a couple of pages whilst the user is paging up and down and then we give it back -- freeing it for someone else". In that case, you can safely TRUNCATE the global temporary table before doing the insert (fast, low overhead, concurrent)



I would

a) trash the temp table, period.
b) just return the result set that you are currently inserting

personally.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

temp tables vs package variables

Jan, June 04, 2003 - 7:59 am UTC

As I understood temporary tables are supported by connection pooling, so web users will see their data there also in case the connections are shared between the users? If yes, so why it does not work in similar way for package variables? What is the difference?

Thanks

Question

A reader, August 21, 2003 - 7:08 pm UTC

Is it a good idea to use ref cursors selecting from global temporary tables which are fed values after some compex calculation in 3 tier
web app with weblogic Oracle.

The issue we have is it returns duplicate rows whenver the Browser refreshes the page i.e it re-calulates the procedure to populate global tEmp table.
For the connection pool the Oracle session is not closed but reused.
Thanx
Will on commit delete rows work for Global Temp table in 3 tier.
Or should I abondon global temp table.
SO whats the alternative



Question Oracle ver 9.2

A reader, August 21, 2003 - 7:10 pm UTC

Is it a good idea to use ref cursors selecting from global temporary tables which are fed values after some compex calculation in 3 tier
web app with weblogic Oracle.

The issue we have is it returns duplicate rows whenver the Browser refreshes the page i.e it re-calulates the procedure to populate global tEmp table.
For the connection pool the Oracle session is not closed but reused.

Thanx
Thanx
Will on commit delete rows work for Global Temp table in 3 tier.
Or should I abondon global temp table.
SO whats the alternative



Tom Kyte
August 21, 2003 - 7:34 pm UTC

it is a really good practice in sqlserver - they sort of invented the need to do so.

me, I prefer just to run a single query personally.


you need to "on commit delete rows" and make sure the connect pool "commits" before reusing the connection.

Thanx much Sir for all your help

A reader, August 21, 2003 - 8:50 pm UTC


GTT and parallel hint

Sami, April 29, 2004 - 2:32 pm UTC

Dear Tom,

Is it okay to use GTT with parallel query? The parallel query spawns more than one session(4 sessions here), hence the question.

1)create global temporary table gtt;

2)insert into gtt as select /*+ parallel(t1,4) */ col1,col2,col3 from t1;

3)select * from gtt;

4)commit;

Thanks in advance

Tom Kyte
April 29, 2004 - 2:50 pm UTC

yes, because your SESSION gets all of the rows here.

I don't see PQ "speeding" that up a whole lot though -- not a simple full table scan without any aggregations or anything.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.