Re: Index reorganization

[Quoted] If you're using Oracle 7.3 and above there is a command 'alter index index_name rebuild' .... by specifying storage parameters, you can reduce the number of extents and remove wasted space. It uses the existing index to create the new one (which will have the same name), so will be much quicker than indexing from scratch.
If you want to automate this for all the indexes in the tablespace, you can write a query based on dba_extents to sum the number of bytes by extent_name (index_name) and then create a set of alter indexes to rebuild all the indexes. This would result in larger indexes than really necessary as it doesn't take account of deleted nodes which might be held in the indexes, but you can refine the process if that bothers you. .... Dave Lane Received on Thu Dec 03 1998 - 00:00:00 CET