Manage Automatic Indexing on Autonomous Database

Automatic indexing automates the index management tasks in Autonomous Database. Auto Indexing is disabled by default in Autonomous Database.

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:

1. Use the DBMS_AUTO_INDEX.CONFIGURE procedure to enable automatic indexing:

   EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');

   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.

2. Use the DBMS_AUTO_INDEX package 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:

1. The following statement disables automatic indexing in a database so that no new auto indexes are created (existing auto indexes remain enabled):

   EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');

When you use SODA with Autonomous Database the following restrictions apply:

• Automatic indexing is not supported for SQL and PL/SQL code that uses the SQL/JSON function json_exists. See SQL/JSON Condition JSON_EXISTS for more information.

• Automatic indexing is not supported for SODA query-by-example (QBE).

See Managing Auto Indexes for more information.