Re: Unifying Temp table behavior across oracle, mssql
Date: Sun, 22 Jun 2008 15:34:40 GMT
"Marcin Wróblewski" <m_wroblewski_at_gazeta.pl> wrote in message
> bobdurie_at_gmail.com pisze:
>> 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
>> 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!!
> > 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'sproducts 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 Received on Sun Jun 22 2008 - 10:34:40 CDT