ORACLE Insert performance on indexed tables

Question

Why Insert statements perform slower on a indexed table?

Answer 1

This is actually the same kind of question as:

Why does it take more time to put all my groceries in the correct place in my kitchen than leaving everything in the bags after I visited my groceries store?

This is because when storing your groceries, you want them on a nice, well known position so that it is easier to find them afterwards.

A database has to do the same.

• If you have a table without index, it can just add new data at the end of the table.
• If you have an index, the database has to perform more work. It will probably still put the record at the end of the table, but additionally it will update its index to make sure that if you want to find that record afterwards, it will find it more quickly than without index.

This also means that adding more indexes will further slow down inserts.

It should be clear that you only want to create an index if you will also use it afterwards. If you only create an index and you are not using it afterwards to improve the performance of a query, there's no need to have the index as it will only slow down the inserts, and not improve any query.