· it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan
· Insert, Update and Delete operations will become very slow if there are many indexes created on a table. This occurs when the Insert, Update or a Delete operation against a table results in all the indexes being updated, reducing query performance.
· Indexes are stored on disk and the amount of space required by the index depends on the size of database table, and the number and type of columns used within the index definition.
· Index needed proper maintenance