| Analyzing indexes... [message #152107] |
Wed, 21 December 2005 02:33  |
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 #152204 is a reply to message #152107] |
Wed, 21 December 2005 11:58  |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
