ORACLE Insert performance on indexed tables

Question

Why Insert statements perform slower on a indexed table?

Answer 1

An INSERT statement has to both add the data to the table data blocks and update any indexes that the table has defined.

Clearly, if you have an index, the insert will need to do some more 'work' to update the index as well.

Answer 2

If you need to delete some data at a later stage from the table you inserted into would it not be a good idea to have an index on the table. The extra time taken to insert the data into the table can be compensated by the deletes running faster on an indexed table (if the indexed columns are used in the delete WHERE clause. ????

Answer 3

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.

Answer 4

Indexes allow you to store more information (in index blocks) about the data which helps retrieving the data easier. Obviously, you are doing additional work initially to benefit later (for selects).