Automatic Indexing-Indexes

Starting with version 19c, the Oracle database ships with an automatic indexing feature. This allows you to delegate some index management features to the database. This feature creates, rebuilds, and drops indexes based on the workload of your application.

Indexes that are managed by this feature are known as auto indexes. Here are the main aspects of automatic indexing:

•\ Identifies columns to be indexed that could potentially improve SQLstatement performance. These are known as candidate indexes.

•\ Automatically creates invisible indexes with the SYS_AI prefix.These new indexes are created as invisible so they are not initiallyconsidered for use by the optimizer.

•\ Tests the invisible indexes to ensure they improve the performance ofSQL statements. If performance improves, then the indexes are madevisible. If there is no performance gain, then the indexes are markedas unusable and are later deleted.

•\ Automatically deletes unused indexes.

Note Automatic indexing is currently only available with the Enterprise Edition installed on an Oracle Exadata machine. On systems not running Exadata hardware, if you attempt to execute packages associated with this feature, you will receive an ORA-40216: feature not supported message.

With non-Exadata systems, you can enable this feature by setting the hidden parameter exadata feature_on to TRUE. However, you should only set this parameter with Oracle’s permission as this has licensing ramifications.

Once automatic indexing is enabled, the database manages the creation, rebuilding, and deletion of indexes. After enabling this feature, the DBA’s job would be to

•\ Manage the configuration via setting various parameters

•\ Review the reports generated by the system

With that introduction, let’s next talk about what the DBA needs to do to manage and configure automatic indexing. You’ll be glad to see your job as an Oracle DBA has not been completely eliminated.