Automatic indexing automates the index management tasks in Autonomous Database. Auto Indexing is disabled by default in Autonomous Database.
Note:Automatic Indexing is only supported with Oracle Database 19c onwards.
Creating indexes manually requires deep knowledge of the data model, application, and data distribution. In the past, DBAs were responsible for making choices about which indexes to create, and then sometimes the DBAs did not revise their choices or maintain indexes as the conditions changed. As a result, opportunities for improvement were lost, and use of unnecessary indexes could be a performance liability. The automatic indexing feature in Autonomous Database monitors the application workload and creates and maintains indexes automatically.
To enable automatic indexing:
- Use the
DBMS_AUTO_INDEX.CONFIGUREprocedure to enable automatic indexing:
This enables automatic indexing in a database and creates any new auto indexes as visible indexes, so that they can be used in SQL statements.
- Use the
DBMS_AUTO_INDEXpackage to report on the automatic task and to set automatic indexing preferences.
Note:When automatic indexing is enabled, index compression for auto indexes is enabled by default.
To disable automatic indexing:
The following statement disables automatic indexing in a database so that no new auto indexes are created (existing auto indexes remain enabled):
See Managing Auto Indexes for more information.