Index.

[rborikar]

Hi Everyone,

I need help from you in the Index area.

I want to know whether Rebuilding of an existing index is faster than droping an recreating it ? and if yes why and how? I checked the Oracle 8 documentation and according to them they prefer to rebuild, and from from performance point of view rebuilding is better as compaired with dropping and recreating it. But I am not convinenced. Pl let me know.


Thanks,

Rajesh

[jmodic]

Rebuild is faster than drop-recreate in most cases.

Some reasons:
- For index to be created the system has to full-scan the table. For index to be rebuild the system has to fast-full-scan the index, which is usually faster since indexes typicaly occupy less blocks than underlying tables.
- For index to be created, the system has to sort all the rows (CPU intensive operation, often also intensive I/O on temp tablespace), while when recreating all the index entries are allready sorted in the original index, so the sorting phase is not needed!

HTH,

[rborikar]

Thank you very much for the prompt reply. One more question : In case of an existing index , if I delete some rows what happens to the nodes for which rows are deleted? and during rebuild such nodes will exists in the BTREE or not?

Thanks,

Rajesh

[maske]

If you delete some rows they still have nodes in index, after rebuild index such nodes will not exist in btree.

[maske]

You can use:
Analyze index xxx validate structure
and then from index_stats view find ratio del_lf_rows_len/lf_rows_len.
Oracle recommends rebuilding if deleted enties represent >=20% of current entries.
del_lf_rows_len = sum in bytes of deleted values from index
lf_rows_len = sum in bytes of current values in index.