Re: Unifying Temp table behavior across oracle, mssql

From: gym dot scuba dot kennedy at gmail <kennedyii_at_verizon.net>
Date: Sun, 22 Jun 2008 18:09:26 GMT
Message-ID: <qzw7k.4009$i5.490@trndny07>

<bobdurie_at_gmail.com> wrote in message
news:a7ef5a91-1578-4431-8458-90d25e9d972c_at_b1g2000hsg.googlegroups.com... On Jun 22, 11:34 am, "gym dot scuba dot kennedy at gmail" <kenned..._at_verizon.net> wrote:
> "Marcin Wróblewski" <m_wroblew..._at_gazeta.pl> wrote in message
>
> news:g3lpti$j6g$1_at_inews.gazeta.pl...
>
> > bobdu..._at_gmail.com pisze:
> >> Hi,
>
> >> I have an java application with a common persistence layer that now
> >> has the need for temporary tables. The need has arisen basically
> >> because we often need to do IN on large java arrays, and are hitting
> >> the bounds on the IN (1, 2, ... 1000s) for both oracle (max 2000 items
> >> in an in expression) and mssql (our driver uses a stored procedure,
> >> can't have too many parameters) (note, support for 10g+ and ms sql
> >> server 2000+).
>
> >> Anyways, i want to create a common temporary table api - i'm hoping
> >> someone can tell me if what i want to do is a BAD idea.
>
> >> The api needs to support sessions in both auto commit and
> >> transactional mode, so for oracle creation of the tables will look
> >> like this:
>
> >> CREATE GLOBAL TEMPORARY TABLE foo (...) ON COMMIT PRESERVE ROWS;
>
> >> Because we use a global pool of connections, and because i want to
> >> free the resources, before the connection goes back in the pool i'll
> >> delete the temp table:
>
> >> TRUNCATE TABLE foo;
> >> DROP TABLE foo;
>
> >> Does this seem like a bad idea? Is what i'm doing above intense
> >> operations? On sql this looks much different, creation:
>
> >> CREATE TABLE #foo (...);
>
> >> Dropping the table is simple - only on oracle does the truncation seem
> >> necessary:
>
> >> DROP TABLE foo;
>
> >> I've noticed a few minor differences, but they don't seem like
> >> showstoppers, and seem explainable based on things i've read. For
> >> instance, on oracle the TABLE is visible to other sessions after its
> >> creation has been committed, but the rows never are. In sql server
> >> the temp tables are never visible to other sessions.
>
> >> Thanks for any help in advance!!
>
> >> Bob
>
> > > Does this seem like a bad idea? Is what i'm doing above intense
> > > operations?
>
> > I don't know MSSQL but on ORACLE it's a very bad idea.
>
> > I would simply
>
> > 1) DELETE FROM FOO;
> > 2) do sth. with foo
>
> > without dropping and creating foo over and over. This table should be
> > created once.
>
> > In fact I would never consider using auto commit.
>
> > CREATE GLOBAL TEMPORARY TABLE foo (...) ON COMMIT DELETE ROWS
>
> > and then
> > 1) do sth. with foo;
> > 2) COMMIT;
>
> I have to agree with Marcin.
> 1. Very poor application design to use auto commit.
> 2. Sql= stands for Structured Query Language not an MS product.
> 3. You are hitting the problem of a db agnostic where not all vendor's
> products support things in the same manner. On Oracle I might not even
> bother with a global temporary table but pass in a collection and join
> that
> with the data. (assuming the collection wasn't so huge that it caused a
> memory problem. eg select .. from aTable a where a.somcolumn in (select
> collection.column_value from table(cast(mycollestion as t_mycollection)));
> assuming a simple collection like a table of numbers. A more compex
> collection would need the column name.
> Jim

Wow, thanks for the quick replies!!!

In response to the first response, i see your point - creating the table over and over is a BAD idea. Of course, this will then lead me to the annoying problem of managing these tables, something i wanted to avoid :) Most of the time the tables are simply a single column of type int or varchar, so i could conceivably just create them and have them around. If the data across sessions doesn't collide it will never be a problem, and i rarely/never need to create more than one at once.

For the second comment:
1) Poor app design to use auto commit - this is a possibility. We use auto commit when have a lot of read only / non-automic operations to make on the database. We fetch a connection, set it into auto commit, then access all the tables we need to in whatever order we like. Because we have to support SQL Server, we have to be careful about doing such things with auto-commit OFF because of possible deadlocking issues. I know the concern doesn't exist on the oracle, but it would lead to far more issues maintaining 2 code bases. I suppose the alternative would be to simply NEVER use auto-commit on oracle, which is something i may consider doing... if theres no risk on oracle, it wouldn't create an issue. Can you tell me why exactly using autocommit  is indicative of bad app design? Based on this, i may simply be able to make this change.
2) Sorry, MSSQL = Microsoft SQL Server, my bad :) 3) I didn't even know collections existed!!! I'll read up on them now, although depending on how general and supported the jdbc api's are, this may put me into a bad position in terms of database agnosticism, but of course this is the cross i bear.

Thank you so much for your replies!!!

Autocommit means that you can have transaction problems. Sure if you only do it during read only then perhaps it is okay. But during a series of insert ,delete, or update operations you would cause data inconsistensies. Also there are times when you want to read a seies of tables as 1 transaction in time and not see the committed data in the midst of your queries. (eg a banking application that is trying to run a report) Jim Received on Sun Jun 22 2008 - 13:09:26 CDT

Original text of this message