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 sense) 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 (OLTP)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 so are an overhead you just have to live with. The benefits far outweigh the drawbacks.

The final disadvantage is that indexes 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 then, 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.

For more help contact one of our partners about their Oracle training courses. Smartsoft Computing offer instructor-led Oracle training courses in the UK, SkillBuilders provide training in the New York/Boston areas in the U.S, and Gogo training offer classroom-quality, on-line training.

Comments for What are the disadvantages of indexes in Oracle?

Average Rating starstarstarstarstar

Click here to add your own comments

Jan 25, 2017
Rating
starstarstarstarstar
Nice explanation NEW
by: Amit Chaurasia

Really a nice explanation of the disadvantages.

Sep 04, 2015
Rating
starstarstarstarstar
A source!
by: Akoko Danny Igwe

....it is a good one....the person that post them is really a source material for my assignment on CMP 231....Igwe Uni Agric mkd ( B. Sc. Maths/ comp. Sci.

May 12, 2015
Rating
starstarstarstarstar
very nice
by: Anonymous

thank u..very nicely explained.

Oct 09, 2014
Rating
starstarstarstar
Good one !!
by: Anonymous

This small article is much more informative. Thanks for it !!

Jul 31, 2012
Rating
starstarstarstarstar
Disadvantages of indexes
by: Ismail

This is one of the best examples.

Jul 31, 2012
Rating
starstarstarstarstar
disadvantages of indexes
by: Anonymous

I feel one of the best example which you have given.

Apr 24, 2012
Rating
starstarstarstarstar
fabulous answer
by: Asif

The answer seems really a symbol of depth of oracle database expertise

Mar 30, 2012
Rating
starstarstarstarstar
feedback
by: sirisha

i liku ur comment

Dec 19, 2011
Rating
starstarstarstarstar
index
by: pradeep

I like your answer

Nov 10, 2011
Rating
starstarstarstarstar
feedback
by: Ramesh

Its a nice answer....

Sep 09, 2011
Rating
starstarstarstarstar
best examples on disadvantage of index
by: arun

this is best definition of disadvantage of index

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.