Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Optimize Oracle SQL with large 'IN' clause

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!

like image 692
NeoNosliw Avatar asked Sep 04 '10 10:09

NeoNosliw


People also ask

Does with Clause improve performance in Oracle?

The Oracle "with" clause will help performance in situations where your query contains several identical sub queries. Instead of re-computing the repeating sub queries, it will query or aggregate once, assign a name to the resulting data and refer to it.


2 Answers

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

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

like image 139
Mitch Wheat Avatar answered Oct 18 '22 14:10

Mitch Wheat


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

like image 29
Hendra Jaya Avatar answered Oct 18 '22 12:10

Hendra Jaya