rebuild index in Oracle

Question

when do I need rebuild an index.

we are using in data warehouse bitmap indexes. if I truncate table and there is no row and after that I insert new rows an index should be rebuilt as far I know because that make sense.

Is always true when new row is updated or inserted or delete no matter if with single statements or using merge statement then Oracle update all type of indexes automatically?

Answer 1

If you truncate a table, all indexes will be USABLE but empty. In fact, if an index is UNUSABLE the truncate will actually mark it as USABLE by the very fact that it knows there are no rows.

When you insert, update, delete, merge, etc.. Oracle maintains the validity of the indexes by rebuilding their leaf and (sometimes) branch blocks with the new or removed entries. This is why having an excessive number of indexes on a table can really slow down DML operations, particularly inserts and deletes. This is true of both bitmap and b-tree indexes.

Some inserts however can be done in direct path mode. INSERT /*+ APPEND */, inserts with parallel dml enabled (which assumes /*+ APPEND */ even if not explicitly stated), SQL*Loader with direct=true, etc.. these will defer index maintenance until the end of the statement. This helps speed up the cost of maintaining indexes considerably for bulk operations. So if you load a million rows, the indexes will be ignored during the load, but after the last row is inserted, it will then rebuild the affected index blocks to get them in sync.

So, as long as your indexes remain in a usable state, they will always be in sync with the data blocks they point to. Indexes can become unusable under these circumstances:

1. You mark it as unusable (alter index ... unusable)
2. You move the table segment (alter table ... move) without the "update indexes" clause.
3. You do a partition exchange with the "excluding indexes" option
4. You use SQL*Loader in direct path but with the SKIP_INDEX_MAINTENANCE option set, or a unique index finds duplicate rows during the index maintenance phase.
5. Any other bulk operation that defers index maintenance and contains an implicit commit fails during the index maintenance phase due to some problem (duplicate keys, out of space, instance crash, etc..). Ideally the data changes would be rolled back in this case, but we've seen issues like this in previous versions of Oracle pop up now and again.

If any of the above reasons cause indexes to become unusable, the indexes or affected partitions of them will need to be rebuilt manually (ALTER INDEX ... REBUILD). Until this is done, changes to the table are no longer kept in sync with the indexes... an unusable index is in fact an empty index, in fact, a non-existent one from a space perspective, just a logical object with no underlying data segment. Rebuilding it actually creates a segment so that it uses space and contains entries. Once rebuilt, it will then be maintained to kept in sync with further data changes.