Thread: Rebuilding of indexes

Hi all,

I have doubt in rebuilding of indexes in oracle.

Please advice me in the following scenario.

i have a table which has indexes. the table data will be emptied once in 3 months. and a new set of data flows in. Please tell me if it is required to rebuild indexes in this case.


thank you,

With best regards,
Vinay
"I have not failed; I have simply found 10,000 ways which do not work!" -- Thomas Edison

In your case it is compulsary to rebuild indexes.

Index is based on columns but concerned with data.
Index maintains the logical mapping of data and mainly works on rowid. If there is no change in data rowid is not changed so no need to rebuild the index only after modifying the column size.
You must rebuild the index frequently (once a week ) after major data changes.

rebuild them only if there is lot of insertion and updation on table

Once data is delted and committed the rowid will be deleted.
The same (deleted) Rowids may or may not repeat again. But the roweid is always unique inside a database.
The index automatically gets extended for every new insert of rows in the table.

Originally Posted by vinayhc

Hi Sir,

Another doubt here, Please throw some light on it,

suppose we have 10000 rows. and then after three months a old set of 10000 is deleted and new set of 10000 comes in, will the row ids change.

and also is row id ever incrementing for a table and never repeats?

also will the index gets extended when ever a new insert happens to the table?

Thanks,

The ROWID is an internal physical address for every row in every nonclustered table in the database.
It is a unique identifier that is not repeated for any two rows across the whole database.
The ROWID is a pseudo-column that never appears in the list of columns when a table is described.
It can be selected from any table, but it can never be modified.
The ROWID is allocated for the row when the row is first inserted.
It remains for the row until the row is deleted or until the table is dropped.

The main objective of rebuild is performance, the index will be updated whenever the data changes (inserts, updates, deletes)
Please refer to this article http://www.dbazine.com/oracle/or-articles/jlewis14