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
Why using RBO? It's like running a Mercedes Benz with a Trabant engine :-)Quote:
Originally posted by m00nminkey
You need to be careful playing with indexes using the RB0 (Rule based) as you effect the database performance!
m00n
And you might not have heard but there will be no RBO in 10i.
Since 1991 in every release Oracle has been saying that RBO would not be supported, but Oracle kernel developers and designers do not know how to rewrite their own code to utilize the CBO features. That is why they still supported RBO. I do not think RBO will be totally eliminated in 10i.
Read 189702.1: Rule Based Optimizer is to be Desupported in Oracle10i.Quote:
Originally posted by tamilselvan
Since 1991 in every release Oracle has been saying that RBO would not be supported, but Oracle kernel developers and designers do not know how to rewrite their own code to utilize the CBO features. That is why they still supported RBO. I do not think RBO will be totally eliminated in 10i.