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:
- Coalesce tablespace
- Alter table tablename shrink space compact
- Deallocate unused space: See these notes on the difference between coalesce and deallocate unused space
- CTAS (or "alter table CUSTOMERS 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