index extent

Some indexes have several extent in my prod DB ( extent > 100 ) how can I get rid off these extents?

once option is to drop and recreate the index with proper storage parameter.This will also enable u to rebuild the index which will increase the performance of the database

cheese
anandkl

The only possible work arond is

drop the index and create with larger INITIALEXTENT and
PCTINCREASE ( decide PCTINCREASE only after considering space in ur INDEX tablespace)

What does it matter , what is the height of the index,is it unbalanced and do u have large number of del_lf_rows,
If yes , then u rebuild them.

Instead of drop and create, use ALTER INDEX ..MOVE .
This is simpler and faster than any other method.

The above is of course a typo. With indexes you use ALTER INDEX ... REBUILD ... , not MOVE as with tables.

If I use rebuild command then I can't change INIALEXTENT I think? Also do I have to use nologging and also can you iron out what do you mean by : decide PCTINCREASE only after considering space in ur INDEX tablespace ? what is the criteria for this.

select 'alter index '||owner||'.'||segment_name||' rebuild unrecoverable tablespace '||tablespace_name||
' storage ( '||chr(10)||' initial '||bytes||' next '||bytes / 4||' pctincrease '||pct_increase||
' minextents 1 maxextents 100) pctfree 0 ;'
from dba_segments
where owner=upper('&SchemaOwner') and segment_type='INDEX' ;


The Above script will generate a another script , which can be used to rebuild all the indexes in a schema , and you can also use it for single INDEX in a particular schema.

And the Initial extent size would be the present size of the index and with this you will never have extents problem.

And your doubt regarding about the initial extentent is also solved.

test this script in u r R&D box and you can safely implement in production..

Hope this will help you out..

thanks,
Madhu

Quote:

---

Originally posted by farrokhp
If I use rebuild command then I can't change INIALEXTENT I think?

---

You can change *all* storage parameters when using index rebuild, including INITIAL.

Could you free the free blocks in the index with the rebuild command too?

I have some free block under the HMW, the i tried to rebuild the index, but after this it continues to have very free blocks. Is it mandatory for this to DROP and RECREATE the index?

Regards

Angel

It will not release the free blocks after rebuild.Then only way is drop and create the index.

cheese
anandkl