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 15:34:40 GMT
Message-ID: <kiu7k.158$%l.49@trndny03>

"Marcin Wróblewski" <m_wroblewski_at_gazeta.pl> wrote in message news:g3lpti$j6g$1_at_inews.gazeta.pl...
> bobdurie_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 Received on Sun Jun 22 2008 - 10:34:40 CDT