Home » RDBMS Server » Performance Tuning » Analyzing indexes...
Analyzing indexes... [message #152107] Wed, 21 December 2005 02:33 Go to next message
kinjal
Messages: 124
Registered: June 2001
Senior Member
Hello there,

I want to know when we need to analyze the indexs
and and when we need to rebuild it..

Also I have one more question.

I have kept one table in KEEP bufferpool.
Will the indexes for that table be in cache or not?
If indexes are not in cache then is there any performance related issue in this?

Please describe in brief..

Kinjal
Re: Analyzing indexes... [message #152133 is a reply to message #152107] Wed, 21 December 2005 05:32 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10706
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> when we need to analyze the indexs

Everytime after there is a major change in data ( a good percentage of data changed using insert,update or delete).
>>and when we need to rebuild it
May be never.
Depends on the type of index you are using.
Search for INDEX REBUILDING in this forum. You will get a ton of postings.
Re: Analyzing indexes... [message #152204 is a reply to message #152107] Wed, 21 December 2005 11:58 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
kinjal wrote on Wed, 21 December 2005 03:33

Hello there,

I want to know when we need to analyze the indexs
and and when we need to rebuild it..

Also I have one more question.

I have kept one table in KEEP bufferpool.
Will the indexes for that table be in cache or not?
If indexes are not in cache then is there any performance related issue in this?

Please describe in brief..

Kinjal


Mahesh answered most of this, I typically gather index stats at the same time I do table stats, with the cascade => true option. As Mahesh said, when data changes.

As far as the keep goes, it applies to objects individually, so a table being in keep would not necessarily mean its indexes are in keep. As to whether that would affect performance, that depends on all kinds of factors, such as whether you are doing an FTS or not, for instance.

Do a search for rebuilding as suggested, it is a long discussed hot topic. In general, don't rebuild, unless there is a specifically observed data to support that it would be helpful, and data before and after measuring the benefit. In general, btree indexes don't ever need to be rebuilt, oracle will grow and shape them into a constant state of balance. But there are exceptions as always, at bitmaps will need rebuilding more than btrees.
Previous Topic: ORA-00604: error occurred at recursive SQL level 1 ORA-04031
Next Topic: suggest
Goto Forum:
  


Current Time: Tue Dec 06 21:43:59 CST 2022