When Not to Use Indexes in Oracle

There are some circumstances where indexes can be detrimental to performance and sometimes those indexes should not exist. The Optimizer will occasionally ignore indexes and consider reading the entire table a faster option.

• A table with a small number of columns may not benefit from an index if a large percentage of its rows are always retrieved from it.
• NULL values are generally not included in indexes. Do not index columns containing many NULL values unless there is a specific use for it such as filtering the column as NOT NULL, thus only scanning the index and the rows without NULL values.
• Indexes should usually be created on a small percentage of the columns in a table. Large composite indexes may be relatively large compared with the table. The relative size between index and table is important. The larger the ratio of index to table physical size then the less helpful the index will be in terms of decreasing physical space to be read. Also many columns not in the index may contain NULL values. It may be faster to read the entire table. Why create indexes?
• Small static data tables may be so small that the Optimizer will simply read those tables as a full table scan and ignore indexes altogether. In some cases the Optimizer may use an index on a small table where it should not since a full table scan would be faster. An exception to this rule will be in mutable joins where unique index hits are often used, even on small tables. If full table scans are faster than index reads in those mutable joins you might want to remove indexes from the small tables altogether or override with hints. Examine your small static data tables. Do they really need indexing ?