Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

select with an "in" clause...

 
hernan silberman
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I need to do a select that looks like this:
select C1, C2, C3 from my_table
where id in ( v1, v2, v3, ..., vN );
The values for the in clause are arbitrary and could be so many that I worry the length of the resultant SQL string could exceed Oracle's limit, if I were to naively generate the String in my Java code.
I've read up on batching but didn't find anything similar for selects of this type. How can I run this query safely using JDBC? I know this is a common thing to do, any help is much appreciated.
thanks...
H
 
Dana Hanna
Ranch Hand
Posts: 227
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
While this approach in reasonable in some instances, if you are worried about exceeding a limit (is there one?), we'd have to know more about why you are doing this.
Perhaps there is a better way.
 
Michael Matola
whippersnapper
Ranch Hand
Posts: 1820
4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I don't know where I'm channeling this from (in other words, verify before coding anything based on what I'm saying), but for some reason I seem to think Oracle has a limit of 1000 (or maybe 1024) items in an "in" list.
 
Ramesh Donnipadu
Ranch Hand
Posts: 100
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If there indeed is a limitation, can you divide the number of IDs into chunks of size 1000 (or whatever is the max), select chunks of 1000s and add more OR clauses as in below?

SELECT XXX FROM TABLE WHERE ID IN (I1, I2, ...Ik) OR ID IN (Ik+1,Ik+2, ..I2k) OR ID IN (I2k+1+...) ...
 
hernan silberman
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No problem... here's the situation:
We have a query tool which allows our users to select a collection of objects from our database based on a simple criteria they provide through our application's user interface. I work at an animation studio, so the user might ask for a list of all shots "done in animation". Shots trickle through the Animation department in an arbitrary order based on complexity, staffing, and numerous other variables.
Our query tool application code is built in such a way that the query it constructs "dumbs down" the request to look like this:
-- Query 1
select shot_id from shots where <programmatically constructed criteria from the query tool UI>;
Once I have this arbitrary list of shot_ids, I need to retrieve a ResultSet from which I can build all of the Shot objects:
-- Query 2
select C1, C2, C3 from shots
where shot_id in ( <the ResultSet from Query 1> );
This is a simplified example, but you get the point. Query 1 and Query 2 are loosely coupled and I can't combine them into one query without having a huge effect on the current codebase. I realize that Query 2 would work as a subquery of Query 1, but I don't have that luxury.
That being said, I am stuck having to run Query 2 once for each row in Query 1's ResultSet, or build a compond string representation of Query 1's ResultSet and construct Query 2 in an ad hoc fashion risking a SQL String that's too big for Oracle.
I'd love to be able to do something like this using JDBC:
PreparedStatement theStmt =
theCon.prepareStatement( "select <cols> from shots where shot_id in ?" );
theStmt.execute("CREATE TYPE shotidlist_type AS table OF number(8)");
oracle.sql.ArrayDescriptor theArrayDesc =
oracle.sql.ArrayDescriptor.createDescriptor( "shot_idlist_type", theConnection );
java.sql.Array theArray =
new oracle.sql.ARRAY( theArrayDesc, theConnection, theShotIds /*int []*/ );
theStmt.setArray( 1, theArray );
ResultSet theResult = theStmt.executeQuery();
... but I've had no luck with this and the more I think about it, I realize that what I'm trying to do is a fairly special case. I'm starting to wonder if it's possible at all?
Any help is greatly appreciated, thanks for reading this far ;-)
hernan
 
hernan silberman
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
One more update, from my Oracle server:
WARNING: java.sql.SQLException: ORA-01795: maximum number of expressions in a list is 1000
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:579)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1894)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:831)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2496)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2840)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:536)
at com.dw.nile.services.sql.test.JDBCTest.runTests(JDBCTest.java:51)
at com.dw.nile.services.sql.test.JDBCTest.<init>(JDBCTest.java:28)
at com.dw.nile.services.sql.test.JDBCTest.main(JDBCTest.java:122)
 
hernan silberman
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Figured I'd post a follow up, as this issue turned out to be a difficult one to research even with the almighty google.com at my disposal.
My DBA and I came up with this solution:
1) Create a temp table in Oracle:
create global temporary table bulk_select(value01 varchar2(255));
In our Java code we do these "in" queries in the following fashion:
String theSQL = "select C1, C2, ..., Cn from table where Cy in (select * from bulk_select)";
// Do an array insert of the keys into bulk_select.
// run theSQL via JDBC like a normal query.
This solution is a bit Oracle-specific as the temporary table implementations on other RDBMSs have different semantics than oracle. In oracle, the temporary table is tied to the session, so all of the rows inserted by a given session are visible only to that session. That makes this work since the select has to become an insert followed by a select.
Thanks for the advice, this worked out splendidly.
Hernan
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic