Re: Unifying Temp table behavior across oracle, mssql

From: Marcin Wróblewski <m_wroblewski_at_gazeta.pl>
Date: Sun, 22 Jun 2008 17:10:02 +0200
Message-ID: <g3lpti$j6g$1@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; Received on Sun Jun 22 2008 - 10:10:02 CDT

Original text of this message