SQL statement ‘IN’ contains too many expressions

I use JDBC to retrieve Oracle database by ‘IN’ statement. But there are too many expressions, about >1000. It looks like:

SELECT * FROM MyTable WHERE name IN (?, ?, ?......); --More than 1000 question signal

开发者_运维百科It causes ‘syntax error or access rule violation’ which means ‘maximum number of expressions in a list is 1000’.

Do I have to do retrieve operation many times (for example, using 100 ‘?’ and retrieve 10 times) and merge the lists? Any better solution? Thanks!

Edit: I have no chance to modify database which is not my work of art.

I'd be rethinking the logic of why you would need to pass in that many things to look for. Perhaps there is a different way to specify what you are looking for.

Alternatively, another option is to create a temporary table, load the keys you are looking for into the table and then do a join between the data table and the table of keys. Then you won't need a in() clause.

If you have that many expressions, it may be easier to insert them into a temp table, then query MyTable against the temp.

the brute force way around this is

(foo in (1,4,8,12,1000))  or (foo in (2333,2443,2453,4665))

where each in clause is less than 1000

Of course the chances of your query being performant isn't very good.

Oracle has issues with an IN list >~1000.

1. JOIN: create a (optionally temporary) table with the list items and join the table into your query. If you use a more durable table then put a session identifier as a key. Sounds like this may not work as you have no access to the schema?
2. UNION (ALL): break out your list into under 1000, and union multiple selects,
3. OR: (already mentioned) or your list

Personally, I prefer a join for this type of stuff.