Hi Gurus,
What is the prerequiste for Rebuilding indexes and what is the procedure .I need command also.help me.
RGds
Babu
Printable View
Hi Gurus,
What is the prerequiste for Rebuilding indexes and what is the procedure .I need command also.help me.
RGds
Babu
How about using Index Organized Tables (IOT )?
VAST
Create table
columns....
.........
)
Reorganize index Tblspc_name < TBl Name>
........
;
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
Just out of nterest, why do you want to rebuild your indexes?
if someone needs to be a guru to know the command to rebuild an index we might as well all retire!Quote:
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
RTFM
How about not using them :-)Quote:
Originally posted by vastdba
How about using Index Organized Tables (IOT )?
VAST
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
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)
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