Oracle Fragmentation

Is Fragmentation important?

Fragmentation can waste space. This could have a small percentage impact on full scan performance. If avoiding fragmentation is a priority for you, consider tuning PCTFREE for your segments. If performance is your priority, index properly. In cases where you have done a large delete resulting in wasted space, consider the online rebuild options available in 12.2...

ALTER TABLE mytable ENABLE ROW MOVEMENT;

Then any of these...

ALTER TABLE mytable SHRINK SPACE;

ALTER TABLE mytable SHRINK SPACE COMPACT;

ALTER TABLE mytable SHRINK SPACE CASCADE;

ALTER TABLE mytable MOVE ONLINE TABLESPACE users;

In previous versions the rebuild needs to happen offline... weigh up the benefits of saved space and a possible slight increase in performance versus a significant outage for large tables...

ALTER TABLE mytable MOVE TABLESPACE users UPDATE INDEXES;

Contains more examples and scripts to identify the issue and calculate benefits of shrinking.

Bibliography