What are the disadvantages of indexes in Oracle?

by haribabu
(bangalore)

WHAT ARE THE DISADVANTAGES OF INDEXES IN ORACLE?

That's a very good question to ask in general of any feature in your database or system because every action you take or option (in the gerneric sens) that you choose when you're designing a database or system is just as likely to have one or more disadvantages as it is to have advantages and it is important to consider both before making a decision.

Back to the specific question - what are the disadvantages of indexes in Oracle?

Indexes have the following disadvantages -

• they increase the disk space requirements of your database
• they slow down dml (i.e. inserts, updates and deletes)
• they increase the maintenance requirements of your Oracle database
• they may make your queries slower instead of faster

That was the list of disadvantages now let's expand on each of these and see how they stack up in the real world and look at the other side of the coin.

Indexes do require more disk space, but disk space is cheap these days so you don't really need to worry too much about this. That is not to say, however, that you should completely ignore this and create hundreds of indexes "just in case".

The next disadvantage is that indexes slow down data manipulation language (dml) statements (inserts, deletes and updates). The more indexes you have in your database and the more data maintenance there is the greater will be the overhead.

This is a concern for transaction-processing systems where there is a high proportion of writes (dml) compared to reads (queries).

For a data warehouse you don't need to worry very much as these are usually updated over night or over a week-end when nobody is using the system. For transaction-processing systems though you need to ensure that any indexes you create will have sufficient benefit for queries to offset the overhead for updates.


Some indexes - such as primary key indexes - are created to ensure data integrity and then are an overhead you just have to live with.

The final disadvantage is that they may actually slow down your queries. You might think that using an index will always make a query faster but this is not true. If you take the extreme example of reading a whole table via the index you're going to read twice as many blocks as needed (one read for an index block and then one read for a data block). Oracle also has several features that aim to optimize i/o (including table/index statistics used by the optimizer and multi-block reads) so most of the time the Oracle database will only use an index to read a small proportion of the data.

In conclusion the, indexes do have some disadvantages particularly in transaction-processing systems but if you a good job on the database design and only create indexes if necessary (and indexes such as the primary key index which enforce data integrity definitely fall into that category) then the benefits will far outweigh the costs. See this performance tuning tutorial for more details on the pros and cons of indexed-reads versus full-table scans or this set of tutorials on Oracle performance tuning. Also worth reading is the Oracle Database Concepts guide which "provides an architectural and conceptual overview of the Oracle database server" if you need further information on the fundamentals of databases.