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:
- Create indexes on columns frequently used in WHERE clauses
ex: SELECT * FROM employees WHERE state = 'CA';The state column is a good candidate.
- 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
- 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
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.