Prev: Heteregenous connections client ==> oracle database ==>sqlserver
Next: How can I alter the parameter nls_sort
From: bobdurie on 22 Jun 2008 10:31
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
From: Marcin Wróblewski on 22 Jun 2008 11:10
bobdurie(a)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;
From: gym dot scuba dot kennedy at gmail on 22 Jun 2008 11:34

"Marcin Wr�blewski" <m_wroblewski(a)gazeta.pl> wrote in message
news:g3lpti$j6g$1(a)inews.gazeta.pl...
> bobdurie(a)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


From: bobdurie on 22 Jun 2008 12:24
On Jun 22, 11:34 am, "gym dot scuba dot kennedy at gmail"
<kenned...(a)verizon.net> wrote:
> "Marcin Wróblewski" <m_wroblew...(a)gazeta.pl> wrote in message
>
> news:g3lpti$j6g$1(a)inews.gazeta.pl...
>
> > bobdu...(a)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!!!
From: gym dot scuba dot kennedy at gmail on 22 Jun 2008 14:09

<bobdurie(a)gmail.com> wrote in message
news:a7ef5a91-1578-4431-8458-90d25e9d972c(a)b1g2000hsg.googlegroups.com...
On Jun 22, 11:34 am, "gym dot scuba dot kennedy at gmail"
<kenned...(a)verizon.net> wrote:
> "Marcin Wr�blewski" <m_wroblew...(a)gazeta.pl> wrote in message
>
> news:g3lpti$j6g$1(a)inews.gazeta.pl...
>
> > bobdu...(a)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


 |  Next  |  Last
Pages: 1 2
Prev: Heteregenous connections client ==> oracle database ==>sqlserver
Next: How can I alter the parameter nls_sort