Q

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?

Thanks!

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.

This was first published in January 2007

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close