Re: Index reorganization

If you are making the Oracle optimizer use rule based optimization, be careful about the statistics automatically created with alter index rebuild. If you WANT statistics, analyze the index after rebuild. The statistics Oracle creates for alter index rebuild are way off.

Dave Lane (dlane_at_pt.lu) wrote:
: 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 Sun Dec 06 1998 - 00:00:00 CST