In an Oracle RDBMS, index splitting is a technique used to improve the performance of an index by redistributing its entries and dividing it into multiple smaller, more manageable parts. This process can be particularly helpful when dealing with large, heavily accessed indexes. Detecting missing indexes and materialized views is crucial to maintain optimal database performance. The following instructions describe how to identify these missing elements and perform index splitting in an Oracle RDBMS:
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.