not all the index need to rebuild, exists a script at MOS (my oracle support) that can help you to know what indexes have benefits if rebuild:
Lists All Indexes that Benefit from a Rebuild (Doc ID 122008.1)
Script to investigate a b-tree index structure (Doc ID 989186.1)
rebuild table? you mean shrink tablespace? It is to reclaim waste space
Actually you're probably much much better off reading this article from 'Mr. Index':
(it ,amongst others, debunks Metalink Note: 122008.1 as well)
One specific scenario is where you do full table scans and have deleted a lot of rows, leaving few rows per block when you could have many, or just an inappropriate high water mark, and you wont' be adding that much data any time soon.
Then again, there are scenarios where normal application updating tables densifies the blocks, and rebuilding them leaves free space that will never be used, slowing down full table scans.
Further, bugs (oracle or application misfeature) may exist that have a net effect of wasting a lot of space.
So... "IT DEPENDS."
Best approach is to assume Oracle does the right thing, unless you see specific evidence that something is acting unexpected, or people have specific complaints where you can show cause.
If LMT with too many extents does not hurt the query performance then why are we still rebuilding table/index even in 11g? Any specific scenario??
In many places lots of DBA "work" is done like a routine just because it has always been done since the days of myth like sacrificing chickens for bountiful rains. The only guaranteed "benefit" is that your DBAs will have plenty of overtime to claim every month.
great and illegal . I can't copy it
You have my permission to copy 989186.1 since I own the intellectual copyrights and Oracle Corporation has copied my material without my permission. (They did acknowledge this for a few months after I complained, but I note that they have since removed the acknowledgement).
Alternatively you can look at: Index Sizing | Oracle Scratchpad
Indeed, I work in an environment where there is over 50 databases to be administered and so we have lots of DBAs interacting with each other.
I'm stunned by this myth of "reorganization". Most of the DBAs move tables + rebuild indexes regularly generating huge redo on a monthly basis. Also provoking indexes to do all the splits again generating even more redo. They claim "it helps performance a lot" however not one is able to quantify and quite justify it other than "less extents less I/O, good". Even when I bring up the existence of shrink they say "do not like it, prefer the classic move". People really have a way of holding on to their good ol' practices of Oracle 8i.
For full table scans (which should never be done on a OLTP scenario) this extent issue would be relevant IF data on the table is the victim of large deles and Oracle hasn't re-used that space yet. If your multiblock reads is a multiple of your extent size, than there won't be any overhead of I/O call, no matter the number of your extents. For OLTP this is not relevant because Oracle will access the table via ROWID.
I rarely have ever seen an index benefit from a rebuild significantly. In my experience what people often understand as "index fragmentation" is often just an unoptimized execution plan due to cardinality issues where oracle ends up fetching a large percentage of the table via single reads on that index.