urgent

[venkattr]

Hi Gurus,
What is the prerequiste for Rebuilding indexes and what is the procedure .I need command also.help me.

RGds
Babu

[vastdba]

How about using Index Organized Tables (IOT )?

VAST

[vastdba]

Create table

columns....
.........


)
Reorganize index Tblspc_name < TBl Name>
........
;

[agasimani]

the syntax for rebuilding indexes is:
ALTER INDEX indexname REBUILD;

If you want to rebuild all the indexes in your schema then do this:
sql>SPOOL 'REBUILD.SQL'
sql>SELECT 'ALTER INDEX ' || INDEX_NAME || ' REBUILD;' FROM USER_INDEXES;
sql>SPOOL OFF
sql>@REBUILD

[slimdave]

Just out of nterest, why do you want to rebuild your indexes?

[pando]

Originally posted by venkattr
Hi Gurus,
What is the prerequiste for Rebuilding indexes and what is the procedure .I need command also.help me.

RGds
Babu

if someone needs to be a guru to know the command to rebuild an index we might as well all retire!

RTFM

[julian]

Originally posted by vastdba
How about using Index Organized Tables (IOT )?

VAST

How about not using them :-)

[m00nminkey]

Why rebuild and not recreate? what are you trying to achieve or fix?
What optimiser you using? (optimiser_mode in the init.ora)

You need to be careful playing with indexes using the RB0 (Rule based) as you effect the database performance!

m00n

[pando]

hmm rebuild is faster you know? and it achieve the same goal as drop and recreate, also if we use online clause we can rebuild the index online without affecting performance (and index is still valid for users)

[m00nminkey]

Yes you are correct the rebuild can be faster, but will not fix
corruptions.

The performance issue is related to the Rule Based Optimser, and not the performance hit to rebuild the index.

e.g. if multiple indexes can be applied to the WHERE clause, and they all have an equal number of columns specified, only the index
created last will be used.

I have experience performance problems when indexes have been recreated, and the performance has taken a dive. (just one to
be cautious about)

rgds,
m00n