Re: Unifying Temp table behavior across oracle, mssql
From: Marcin Wróblewski <m_wroblewski_at_gazeta.pl>
Date: Mon, 23 Jun 2008 20:35:06 +0200
Message-ID: <g3oq8u$8kg$1@inews.gazeta.pl>
>> <bobdu..._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 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)
>> Jim
Date: Mon, 23 Jun 2008 20:35:06 +0200
Message-ID: <g3oq8u$8kg$1@inews.gazeta.pl>
bobdurie_at_gmail.com pisze:
> 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
>>
>> 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 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)
>> Jim
> > 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
--- > 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. Using autocommit for read-only operations doesn't make sense. You should commit only (and always) when you have to. > 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. No no no - you should simply create those temporary tables during development (like other tables in your system). Period. I suppose the idea of temporary tables is totally different in MSSQL and ORACLE and that is why you got me wrong. > 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. In my opinion temporary tables are perfect for what you're trying to do. I don't see a good reason to use collections here.Received on Mon Jun 23 2008 - 13:35:06 CDT