 | |
Unifying Temp table behavior across oracle, mssql
comp.databases.oracle.server comp.databases.oracle.server
 |
| | Thread Tools | Display Modes |
#21
 
| |||
| |||
|
Re: Unifying Temp table behavior across oracle, mssql -
06-22-2008
, 01:09 PM
|
"Marcin Wróblewski" <m_wroblew... (AT) gazeta (DOT) pl> wrote in message news:g3lpti$j6g$1 (AT) inews (DOT) gazeta.pl... bobdu... (AT) gmail (DOT) 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 |
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 oftype 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
#22
| |||
| |||
|
|
"Marcin Wróblewski" <m_wroblew... (AT) gazeta (DOT) pl> wrote in message news:g3lpti$j6g$1 (AT) inews (DOT) gazeta.pl... bobdu... (AT) gmail (DOT) 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 |
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 oftype 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
#23
| |||
| |||
|
|
bobdu... (AT) gmail (DOT) com> wrote in message news:a7ef5a91-1578-4431-8458-90d25e9d972c (AT) b1g2000hsg (DOT) googlegroups.com... On Jun 22, 11:34 am, "gym dot scuba dot kennedy at gmail" kenned... (AT) verizon (DOT) net> wrote: "Marcin Wróblewski" <m_wroblew... (AT) gazeta (DOT) pl> wrote in message news:g3lpti$j6g$1 (AT) inews (DOT) gazeta.pl... bobdu... (AT) gmail (DOT) 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 oftype 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 |
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
#24
| |||
| |||
|
|
bobdu... (AT) gmail (DOT) com> wrote in message news:a7ef5a91-1578-4431-8458-90d25e9d972c (AT) b1g2000hsg (DOT) googlegroups.com... On Jun 22, 11:34 am, "gym dot scuba dot kennedy at gmail" kenned... (AT) verizon (DOT) net> wrote: "Marcin Wróblewski" <m_wroblew... (AT) gazeta (DOT) pl> wrote in message news:g3lpti$j6g$1 (AT) inews (DOT) gazeta.pl... bobdu... (AT) gmail (DOT) 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 oftype 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 |
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
#25
| |||
| |||
|
|
bobdu... (AT) gmail (DOT) com> wrote in message news:a7ef5a91-1578-4431-8458-90d25e9d972c (AT) b1g2000hsg (DOT) googlegroups.com... On Jun 22, 11:34 am, "gym dot scuba dot kennedy at gmail" kenned... (AT) verizon (DOT) net> wrote: "Marcin Wróblewski" <m_wroblew... (AT) gazeta (DOT) pl> wrote in message news:g3lpti$j6g$1 (AT) inews (DOT) gazeta.pl... bobdu... (AT) gmail (DOT) 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 oftype 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 |
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
#26
| |||
| |||
|
|
bobdu... (AT) gmail (DOT) com> wrote in message news:a7ef5a91-1578-4431-8458-90d25e9d972c (AT) b1g2000hsg (DOT) googlegroups.com... On Jun 22, 11:34 am, "gym dot scuba dot kennedy at gmail" kenned... (AT) verizon (DOT) net> wrote: "Marcin Wróblewski" <m_wroblew... (AT) gazeta (DOT) pl> wrote in message news:g3lpti$j6g$1 (AT) inews (DOT) gazeta.pl... bobdu... (AT) gmail (DOT) 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 oftype 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 |
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
#27
| |||
| |||
|
|
bobdu... (AT) gmail (DOT) com> wrote in message news:a7ef5a91-1578-4431-8458-90d25e9d972c (AT) b1g2000hsg (DOT) googlegroups.com... On Jun 22, 11:34 am, "gym dot scuba dot kennedy at gmail" kenned... (AT) verizon (DOT) net> wrote: "Marcin Wróblewski" <m_wroblew... (AT) gazeta (DOT) pl> wrote in message news:g3lpti$j6g$1 (AT) inews (DOT) gazeta.pl... bobdu... (AT) gmail (DOT) 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 oftype 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 |
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
#28
| |||
| |||
|
|
bobdu... (AT) gmail (DOT) com> wrote in message news:a7ef5a91-1578-4431-8458-90d25e9d972c (AT) b1g2000hsg (DOT) googlegroups.com... On Jun 22, 11:34 am, "gym dot scuba dot kennedy at gmail" kenned... (AT) verizon (DOT) net> wrote: "Marcin Wróblewski" <m_wroblew... (AT) gazeta (DOT) pl> wrote in message news:g3lpti$j6g$1 (AT) inews (DOT) gazeta.pl... bobdu... (AT) gmail (DOT) 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 oftype 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 |
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
#29
| |||
| |||
|
|
bobdu... (AT) gmail (DOT) com> wrote in message news:a7ef5a91-1578-4431-8458-90d25e9d972c (AT) b1g2000hsg (DOT) googlegroups.com... On Jun 22, 11:34 am, "gym dot scuba dot kennedy at gmail" kenned... (AT) verizon (DOT) net> wrote: "Marcin Wróblewski" <m_wroblew... (AT) gazeta (DOT) pl> wrote in message news:g3lpti$j6g$1 (AT) inews (DOT) gazeta.pl... bobdu... (AT) gmail (DOT) 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 oftype 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 |
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
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.