How much benefit is there in rebuilding a table/index so that it fits in a single extent ?

The Oracle (tm) Users' Co-Operative FAQ

How much benefit is there in building a table/index that fits into a single extent

Does number of extents matter ?

Some DBA's are reluctant to allow more than a few extents in any segment with the mistaken belief that such "fragmentation" degrades performance. Within reason, the performance impact of multiple extents is almost insignificant if they are sized correctly

Does extent size matter ?

Yes, extent size does matter, but not greatly. Nevertheless, all extents should be a multiple of the multiblock read count. Otherwise, when a full table or index scan is performed, an extra multiblock read will be required to read the last few blocks of each extent, except probably the last one. This is because multiblock reads never span extent boundaries, even if the extents happen to be contiguous.

Consider for example the table T1. It is comprised of 8 extents of 50 blocks each. The first block is the segment header, there are 389 data blocks in use, and there are 10 free blocks above the high water mark. With a multiblock read count of 16 blocks, and assuming none of the blocks are already in cache, a full table scan of this table will require 4 data block reads per extent, except the last - a total of 31 multiblock reads.

If the table is rebuilt as T2 with an extent size that is an exact multiple of the multiblock read count, then the number of multiblock reads required to scan the table is minimized. Assuming the table is now comprised of 5 extents of 80 blocks each. A full table scan now requires 5 multiblock reads per extent, or a total of 25 multiblock reads.

Please note that it was not the reduction in the number of extents as such that made the difference. There would be no further saving in rebuilding the table with a single extent of 400 blocks. The number of multiblock reads required to scan the table would still be 25..

Further reading: