Problem solve Get help with specific problems with your technologies, process and projects.

Determining which columns should be indexed

This is a warehouse database, and some of the tables are very large. I have a complex SQL query in Oracle8i that is using some of the large and small tables. I've used the explain plan to see the execution path. However, I am unable to determine what are the columns to be indexed.

Here are a couple of good rules of thumb to follow to determine which columns should be indexed:

  1. Create indexes on columns frequently used in WHERE clauses
        ex:  SELECT * FROM employees WHERE state = 'CA';
    The state column is a good candidate.
  2. Create indexes on columns that have lots of distinct values (10% or less of values are duplicate)
    ex: SSN (social security number), PHONE_NUM (phone number) = good candidates
    GENDER (male or female) or STATUS (yes/no or on/off) = bad candidates
  3. Create indexes on columns frequently used to join tables
        ex:  SELECT dept_name, emp_no, emp_name FROM emp, dept WHERE emp.dept_no
    = dept.dept_no ;
    The emp.dept_no column is a good candidate
These are three of the most common ways to determine where to create indexes but there are many more.

You can get more info by reviewing the Oracle Database Performance Guide and Reference and the Oracle Application Developers Guide Fundamentals documents located on TechNet: http://technet.oracle.com.

For More Information

  • Dozens more answers to tough Oracle questions from Karen Morton are available here.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.