Re: Unifying Temp table behavior across oracle, mssql
Date: Mon, 23 Jun 2008 08:25:31 -0700 (PDT)
On Jun 22, 2:09 pm, "gym dot scuba dot kennedy at gmail" <kenned..._at_verizon.net> wrote:
> <bobdu..._at_gmail.com> wrote in message
> 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
> > > 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
> 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 auto-
> commit 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)
Ok so using autocommit for read-only operations is NOT an app design issue, its a perfectly reasonable thing to do - if anyone feels this is incorrect please let me know.
Based on the discussion we've had, i now understand that what i need to do is to basically use a pooled approach on oracle, where the temp tables are created on demand, but never "re-created". The api will likely test to see if the table exists and if so create it, otherwise just use it. On sql server i'll create on demand every time.
The use of collections in oracle may be a possibility, but because there seems to be a lack of support for other jdbc drivers i can't really use them without lots of non-agnostic code. This is a tricky rope to walk, but its normally less costly to have a poorly performing application with fewer non-agnostic bugs if you know what i mean. The majority of the users don't use oracle so i can't slide in tons of oracle only code for fear a full qa round won't catch the issues.
Thanks again for all your help!
Bob Received on Mon Jun 23 2008 - 10:25:31 CDT