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:
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):
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.
Parent topic: Performance Monitor and Management