What are good candidates for creating indexes on beside pk and unique columns?

Question

I'm always asking myself should I create an index or not in my Oracle tables, what criteria in a table makes the index worth creating? And is it also a criteria in the query? And are there (with respect to Oracle) different kinds of indexes you can create based on some factor(s)?

Answer 1

It's all about what queries are going to run against the table, how fast they're going to be, how fast you need them to be and how often they're going to be run. If you need to be able to find records quickly based on it, index it.

Answer 2

Your queries.

You must look or better yet profile your client app code to see what fields are being used most often and /or have performance issues. If your app is not yet built, think about how the data is going to be used. For instance; If it is a table that will be used heavily for reports, and you have a datetime field, you most likely need an index (compound or not) on that field.

Answer 3

Oracle automatically creates a unique index on a column (or set of columns) when a UNIQUE constraint is created. The index is used in the enforcement of the constraint.

Oracle also automatically creates a unique index on a column (or set of columns) when a PRIMARY KEY constraint is created. This index is used to enforce the constraint as well. There is little, but some, difference between a PRIMARY KEY constraint and a UNIQUE constraint.

Also, when a column (or set of columns) is frequently used to join a pair of tables, there should be a foreign key relationship specified. For this to be done, the referenced column(s) must be the the primary key on the referenced table, which in turn means there will be a unique (primary key) index on that referenced table.

Further, when a particular column (or set of columns) is frequently used in WHERE clauses, it makes sense to create an index, unique or otherwise. Columns with particularly low cardinality (low number of unique values relative to total row count), BITMAP indexes may be advantageous. Pros and cons of BITMAP indexes could be a long discussion.

The order of the columns in multi-column indexes should be considered carefully. First, a multi-column index does not work at all like individual single-column indexes on the same columns, but Oracle rarely (if at all, especially without any hints) uses more than one index on a single query (BITMAP indexes are a possible exception). If you typically have columns A, B, and C in your WHERE clause, you may want an index on A, B, and C. However, if you also often use A and C in a WHERE clause, without B, then you would probably want to order the columns in your index as A, C, B. Such an index can also be used when only A is in your WHERE clause. Put simply, Oracle can use a subset of the columns in an index only by using a prefix of the index, not a random assortment of the columns in the index.

It's also important to note that the more indexes you have on a table, the slower the writes to that table will be. Just consider all the work Oracle has to do to update the table and all of the indexes associated with it. BITMAP indexes can be even more of an impact.

As a final note, EXPLAIN PLAN is your friend. If you find commonly-run queries doing full table scans on large tables, an index may be in order.

Answer 4

A rule of thumb is to create indexes on foreign keys (PKs often have indexes created automatically), fields involved in joins, where clauses or ordering. Indexes havea a cost to inserts, so don't create them willy-nilly on everything. Chose the stuff used most frequently and the queries that have the longest running times to see where you might need to index. Check you query execution plans (different databases call this something differnt but all havea way to see how the query is going to use indexes)to see if the indexes are being used once you create them. An index that nothing is using is just adding to insert and delte time with no correcponding imporvement in select time.

Answer 5

Totally depends on what queries you run against the table.

Likely candidates include:

1. Fields involved in a join
2. Fields that are part of a WHERE clause
3. Fields part of an ORDER BY

Please don't take this to mean you should create a whole bunch of indexes on a table, each on a single field. I see this a lot, and its almost always the wrong answer. A few additional considerations:

• Consider compound indexes, or especially covering indexes.
• Consider the selectivity of a field when deciding if it should be indexed at all, or what order to place it inside an index.

Answer 6

Some good ideas here. Pay particular attention to queries with performance issues, and try to create indexes with high selectivity.

As an aside, it's not a bad idea to monitor your index usage:

ALTER INDEX <index> MONITORING USAGE;

Periodically you can check the value of v$object_usage.used to see if the index has ever been accessed since you turned on monitoring:

SELECT i.table_name, i.index_name, i.tablespace_name, bytes/1000000 mb, u.used
  FROM dba_indexes i JOIN dba_segments ON (i.index_name = segment_name)
          LEFT OUTER JOIN v$object_usage u ON (i.index_name = u.index_name)
 WHERE i.owner = <schema>
   AND NVL(USED,'NO') = 'NO'
   AND i.table_name = <table of interest>
 ORDER BY bytes DESC;

I've been able to prune several indexes with the information this provides.