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

When to develop an index

On what basis should someone consider developing an index for an Oracle database?
The following information comes from the "Oracle application developer's guide -- Fundamentals" documentation (chapter 5):

Use the following guidelines for determining when to create an index:

  • Create an index if you frequently want to retrieve less than 15% of the rows in a large table. The percentage varies greatly according to the relative speed of a table scan and how clustered the row data is about the index key. The faster the table scan, the lower the percentage; the more clustered the row data, the higher the percentage.
  • Index columns used for joins to improve performance on joins of multiple tables.
  • Primary and unique keys automatically have indexes, but you might want to create an index on a foreign key; see Chapter 4, "Maintaining data integrity through constraints" for more information.
  • Small tables do not require indexes; if a query is taking too long, then the table might have grown from small to large.
  • Some columns are strong candidates for indexing. Columns with one or more of the following characteristics are candidates for indexing:

  • Values are relatively unique in the column.
  • There is a wide range of values (good for regular indexes).
  • There is a small range of values (good for bitmap indexes).
  • The column contains many nulls, but queries often select all rows having a value. In this case, a comparison that matches all the non-null values, such as:
  • WHERE COL_X > -9.99 *power(10,125)

    is preferable to


    This is because the first uses an index on COL_X (assuming that COL_X is a numeric column).

    Columns with the following characteristics are less suitable for indexing:

  • There are many nulls in the column and you do not search on the non-null values.
  • LONG and LONG RAW columns cannot be indexed.
  • The size of a single index entry cannot exceed roughly one-half (minus some overhead) of the available space in the data block. Consult with the database administrator for assistance in determining the space required by an index.

    Dig Deeper on Oracle database design and architecture

    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.

    Start the conversation

    Send me notifications when other members comment.

    Please create a username to comment.