Fragmentation and Data Reorganization

As users update, add, or delete records from a database, table and index data blocks on disk can become fragmented or non-contiguous. This inefficient physical data block layout can cause query performance to degrade due to added disk head movements and added I/O response time; additionally inefficient data block layout wastes disk space. The ability of Oracle to reorganize data and restore more efficient data block layout on disk is important to improve overall database performance and disk space utilization.

Avoiding Data Fragmentation

The easiest way to avoid performing data reorganizing operations is to avoid data fragmentation in the first place. Oracle has a number of recommendations. First, set all the extents in a tablespace to the same size. The benefits of a single extent size within a segment are: there are no unusable small free extents, free space is usable by any segment, and it's easier to administer. Second, if there are different size tables, segregate segments into tablespaces based on expected segment size. Choose tablespaces for segments based on three recommended extent sizes: 128KB, 4MB, or 128MB. For example, segments smaller than 128MB should be placed in tablespaces with 128KB extent size. Third, divide temporary and rollback tablespaces into 1024 extents to get optimal performance. All segments less than 128GB should have less than 1024 extents. Oracle recommends large objects be partitioned into smaller segments using the partitioning option.   

You may also consider using a locally managed tablespace, which manages space allocation so as to reduce fragmentation. 

Correcting Data Fragmentation

You can correct data fragmentation by reorganizing the data in your tables and indexes.   However, as the amount of data becomes large, the time to perform a reorganization can become very long.  Traditional techniques for rebuilding tables and indexes require taking the table or index off-line during the rebuild process, and therefore can require extensive planned downtime.  Oracle8 introduced partitions, which allow you to divide and conquer the problem by breaking it into many smaller problems.  Oracle8i does one better.  It allows data to be reorganized while it is online and users are accessing the underlying table or index.