Re: When should one rebuild an index?

On Dec 27, 7:12 am, Robert Klemme <shortcut..._at_googlemail.com> wrote:
> Maybe we need something like BAARF for index rebuilding - or even
> rebrand BAARF to "battle against any rebuild flapdoodle".
>
> That's the bad side of internet not forgetting anything, especially if
> someone seems to be more proficient in search engine tuning of his pages
> than in dealing with Oracle on a scientific basis. :-)
>
> Cheers
>
>         robert

It seems that the links I pointed to in my original post in this thread missed one of the reasons for rebuilding indexes, so that the indexes can be moved from one tablespace to another, for instance if someone wanted to move their indexes from a tablespace with a 2KB block size to a tablespace with a 32KB block size, with the database instance running on a Windows server.
http://www.freelists.org/post/oracle-l/Setting-db-32k-cache-size-breaks-11107,6

For thoughts on rebuilding indexes in larger block size tablespaces, see the following link:
http://richardfoote.wordpress.com/category/index-block-size/

Search engine tuning of pages... that reminds me of a couple threads on Oracle's OTN forums. Ever wonder how the redo log switches are controlled by the size of the log_buffer?: http://forums.oracle.com/forums/thread.jspa?threadID=837258

For those willing to perform searches, the effects of efforts toward tuning search engine results to return one's pages first by repeatedly posting multiple links to one's sites may quite easily backfire. Ignore the first couple posts (a legitimate question) and the last couple posts (a completely different topic) in the following thread: http://forums.oracle.com/forums/thread.jspa?threadID=837914&tstart=0

The above thread was created as a result of this Statspack thread, which was removed from the Oracle OTN forums, but is still in the Google cache:
http://74.125.113.132/search?q=cache:URVx_XbrD74J:forums.oracle.com/forums/thread.jspa%3FmessageID%3D3159904%26tstart

To the OP, sorry for taking the thread a bit off topic. It is important to not only understand when an index should be rebuilt, but also to understand the potential problems with rebuilding. Just using ANALYZE INDEX VALIDATE STRUCTURE against an index generates locks on the underlying table which may cause problems: http://forums.oracle.com/forums/thread.jspa?threadID=576921

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sat Dec 27 2008 - 08:11:10 CST