Ask the Expert

Optimize SQL query on table with 50 million records

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!

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: