Oracle Database Discussions

Hello All,

I think this topic has been raised by many times till now...But i would like to know as i am very curious.

A table of size 6GB having btree index of size 300MB, Very high CPU load in this server(i think because of I/O), after rebuilding this index size came down to 250MB.

Now all my seniors commanded me to rebuild all the indexes in this database which are huge in size, so that I/O and CPU will come down to normal.

I had argument with my senior for reason why we need to rebuild indexes ???
Answer: As Oracle accesses this index it has scan lot many blocks due to fragmentation. Also Oracle has to scan all the blocks below high water mark, where some blocks are having less rows.

My doubt is: when index is fragmented (means canditates which after rebuild reduces its size) does Oracle scans unwanted blocks below high water mark is wrong , becuase INDEX and TABLE are two different concepts, INDEX keeps information about which all blocks it need to scan for required values. Hence Oracle will not scan unwanted blocks in INDEX.

Do may doubt is right or wrong????

Some details:
Index is in DMT tablespace.
Oracle 8i version.
Sun solaris with 5.8 version, having 8 CPU's and 6 GB RAM
None of the indexes are having height more than two.

Please HELP me in this concept....i want to prove my seniors.

-Yasser