Choose Indexing Column

It is common to ask on which column of a table I will create index. In this topic I want to give an idea about the criteria based on which you can decide on which column you will make an index.

•Consider to create an index for the column that is used frequently in WHERE clause. For example, if SELECT * FROM test WHERE A=2; query is executed frequently then consider to create an index on column A.

•Consider to create an index for the columns that are used frequently to join tables in SQL statements. For example if select a.n, b.b from table_a a join table_b b on a.k=b.k; query is executed frequently then consider to create index on column k of table b and column k on table a.

•Choose to create index on the columns of a table that have high selectivity. High selectivity index that the column has few duplicate values. If columns are defined as unique and primary keys then oracle automatically create index on these columns. Lower selectivity means that many row of the column have the same value. Indexing low selectivity columns can be helpful if the data distribution is skewed so that one or two values occur much less often than other values.

•Don't create standard B-tree indexes on column having few distinct values. If there are many duplicate values in a column, consider to create bitmap index unless the index is modified frequently, as in a high concurrency OLTP application.

•Don't create index on a column that is modified highly. Update statement that modify indexed column and delete or insert statement that modify indexed table take longer time than if there is no index. Such statement also modify data highly on index along will highly generate redo and undo.

•Don't create index on columns that appear in WHERE clause and uses a function other than MAX , MIN. In these case consider using function-based indexes.

•Consider to create index on foreign keys columns in which a large number of concurrent INSERT, UPDATE, and DELETE statements access the parent and child tables. Such an index allows UPDATEs and DELETEs on the parent table without share locking the child table.

•While creating a new index always consider whether performance gain for queries is worth the performance loss for INSERTs, UPDATEs, and DELETEs.