Space Management   «Prev 

How to reduce fragmentation in Oracle?

COALESCE tablespace

Question: What are the different ways to defragment a table? I have a table with some free space after I executed a delete and wanted to know what options I have for defragmenting the table.
Answer: Fragmentation occurs as DML changes Oracle rows form their untouched state and intensive DML activity can cause full-table scans and index range scans to run too long, furthering the need to defragment the table to reduce block I/O.
Oracle provides many methods for defragmenting a table, and any process that copies all of the table rows (i.e. create table as select) can be used to defragment a table:
  1. Coalesce tablespace
  2. Alter table tablename shrink space compact
  3. Deallocate unused space: See these notes on the difference between coalesce and deallocate unused space
  4. CTAS (or "alter table xxx move"): This will defragment the table by copying the rows into their pristine state. You can also use dbms_redefinition to defragment an Oracle table.

To fix honeycomb fragmentation you need to combine adjacent free segments into one by issuing a coalesce statement
The previously described tablespace has two 500 KB holes

With the COALESCE command, the free space in the tablespace is moved to the end of the tablespace

The tablespace row has enough room to accommodate another of the larger extents