Oracle provides table and index maintenance tools (Oracle 10g online reorganization).
In order to keep Oracle running fast, Oracle chose not to incur the overhead of
- coalescing table rows and
- restructuring indexes
during peak update times.
This is why we have the DBA maintenance utilities.
The challenge is knowing when to use the Oracle 10g DBA tools.
Oracle 10g and beyond has offered huge improvements in Oracle indexing, especially related to the detection of missing indexes and materialized views and the automation of index histogram detection for the SQL optimizer.
We also see these improvements to table maintenance in Oracle 10g:
Oracle Database 10g includes the following online data reorganization enhancements:
- Online table redefinition enhancements
- Easy cloning of indexes, grants, constraints, etc
- Convert from LONG to LOB online
- Allow unique index instead of primary key
- Change tables without recompiling stored procedures
- Online segment shrink
Despite all of the great automated tools, the Oracle DBA must still perform routine table and index maintenance to keep highly active databases performing at peak levels.