Optimize SQL query on table with 50 million records

We have a table with about 50 million records. We are executing a query that takes one or two seconds to complete in Oracle 10g, but we need it to take less than half a second.

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?


You should examine the optimizer execution plan for the query using EXPLAIN PLAN, and also trace the execution using DBMS_SUPPORT.START_TRACE. These will tell you exactly what Oracle is doing to execute the query. How many rows are typically returned by the query? If more than about 20% of the table, a full table scan may be more efficient than a lookup using the primary key index -- but again, first you must observe the current execution plan.

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.

Dig Deeper on Oracle and SQL