We have a table with about 50 million records with the following structure:
- column1: integer
- column2: integer
- column3: varchar
- column4: varchar
- column5: date
The primary key of the table is (column1,column2).
We need to execute the following query on this table:
select * from table where column1 in (...set of values...) and column2 in (...set of values...)
Both the sets of values used for the "in" clause have a size which may be from 30 to 60 distinct values.
Now this query needs about one or two seconds to complete in Oracle 10g, but we need this query to be executed in less than half a second, in the fastest possible way.
What could be a solution to optimize this query?
Are the in-list values available in the database? If so, a join or subquery may be more efficient than specifying them as literals. You can also experiment with optimizer hints; take a look at the Oracle Performance Tuning manual for additional ideas.
This was first published in January 2007