What are the disadvantages of indexes in Oracle?
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