I need to index some tables and for that i found that there are different kinds of indexing. I am kind of clueless on their proper place of usage.
Should i create index on all the fields that want to retrieve quickly or Should i create composite index on couple of fields?
Is it OK to create composite indexes along with the normal indexes? suppose i have 3 fields :


Can i individually index each field and then have different composit indexes on these fields as well?
For example by writing :

Create index indx_name on tblTest(FirstName);
Create index indx_lastname on tblTest(Last name); 
Create index indx_state on tblTest(state);
create index indx_cmp_fls on tblTest(FirstName,LastName,state); 
create index indx_cmp_lfs on tblTest(LastName,FirstName,state);
create index indx_cmp_sfl on tblTest(state,FirstName,LastName);
create index indx_cmp_slf on tblTest(state,LastName,FirstName);

Is it OK?
Is it suggested? if not what is the best way ? Is it also OK to have index on all fields ?

  • @Mat: That is wrong: sqlfiddle.com/#!4/f2a31
    – miracle173
    Commented Sep 20, 2014 at 8:44
  • 2
    You should be aware that indexing all combinations of all fields is not optimal - otherwise the database engine would do this automatically. You pay a price for this with inserts and updates (lots of indexes to be synced). Try and establish your usage patterns and index accordingly.
    – Vérace
    Commented Sep 20, 2014 at 10:33
  • 1
    Check out this website. It really explains how to use indexes: use-the-index-luke.com
    – user1822
    Commented Sep 22, 2014 at 7:20

2 Answers 2


Your question amounts to, "How do I use indexes?" You should read some Oracle documentation to get up to speed on how to use indexes and how to determine what columns to index. Oracle has an entire library of free online books and documentation for their products. A few good links to start:

  1. The chapter on Indexes and Index-organized Tables from Oracle Database Concepts (the entire book is definitely worth reading)
  2. The Oracle Database Performance Tuning Guide on Choosing Columns and Expressions to Index
  3. Using Indexes in Database Applications from the Oracle Database Advanced Application Developer's Guide

I completely agree with dartonw's comments about reading through the guide. That's the only real way to start understanding the concept of indexing as it relates to Oracle and the Optimizer.

One thing I will add to it though is this - Philosophically, as a DBA, I rarely decide what columns to index on my own. What I mean by this is that I go back to the developers and ask them for the use cases of searching that table. If the table has five columns, and the dev tells me "we'll be joining this table to that table by columns a and b to retrieve columns c,d,e", that tells me that I need the index on columns a and/or b. The Developers/business users and their SQL should dictate what columns are indexed, not what you have read about in a book. Each app is different, and your indexing should be built specifically around data storage and retrieval of that app.

I hope that makes sense.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.