Optimize Oracle SQL with large 'IN' clause

Question

Here I have a query like below:

SELECT field
FROM table
WHERE value IN ('val1', 'val2', 'val3', ... 'valn')

Let's say there are 2000 values inside the IN clause, the value doesn't exist in other table. Do you have any idea to speed up this operation?

The question is open to accept any kind of methods..

Thanks!

Answer 1

1. Create an index that covers 'field' and 'value'.

2. Place those IN values in a temp table and join on it.

Answer 2

SELECT field
FROM table
WHERE value IN SELECT somevalue from sometable

As far as i know, you will face another problem. That will be the limitation of 'IN' clause. Using this, you can avoid that and hopefully fasten your query