Re: Maintenance on Indexes

In article <01bd5833$bbf1bc00$a504fa80_at_mndnet>, "news.insysnc.net" wrote: OK,

If you are at a sufficiently high enough release of Oracle, use the REBUILD option. However, I fail to see what coalescing free space has got to do with rebuilding indexes?

You rebuild an index beacuse it has become 'untidy'. Should there be a lot of insert and delete avtivity against a table, the B*Tree may become a bit of a mess and additionally have a large amount of wasted space. This or possibly a large number of extents, can dictate a rebuild.

Fragmentation can relate to extents, and setting PCTINCREASE for the tablespace to 1 IS a good idea (although it should be set to 0 for indexes and tables).

regs

Neil Chandler

>
>The correct command to be used to rebuild the index on the desired
>tablespace is:
>
>ALTER INDEX index_name REBUILD TABLESPACE tablespace_name;
>
>
>Better yet, set the PCTINCREASE for the tablespaces in question to 1 be it
>for tables or indexes.
>In this case the SMON process COALESCES the contiguous freespace. It may
>take SMON upto
>to 15 to 20 minutes to do this. But it does the job and you do not need to
>
>rebuild the indexes or even worry about the fragmented tablespace for
>tables.
>
>suresh.bhat_at_mitchell-energy.com
>
>
>Gillian <gmuruga_at_entergy.com> wrote in article
><350E980F.6E456F8B_at_entergy.com>...
>> I need to cut down the fragmentation on the indexes in Financials.
>>
>> Is there a statement I can use to DROP and CREATE all simultaneously.
>> There are about
>> 12526 indexes. I could probably use the DBA_IND_COLUMNS table together
>> with the
>> DBA_INDEXES table.
>>
>> Let me know.
>> Much appreciated.
>>
>> Gillian
>> zmkj04_at_hotmail.com
>>
>>
Received on Tue Mar 31 1998 - 00:00:00 CST