In Oracle Database 19c, Automatic Indexing is a feature introduced to improve SQL performance by automatically creating, rebuilding, and dropping indexes based on workload patterns, without requiring manual intervention. This feature is part of the Oracle Autonomous Database offering but can also be used in on-premises Oracle 19c Enterprise Edition with the Oracle Tuning Pack.

How Automatic Indexing Works

1. Monitoring SQL Workload:
   • The database continuously monitors queries and identifies those that can benefit from new indexes.
2. Index Candidates Generation:
   • It evaluates execution plans and simulates the benefit of potential indexes (virtual indexes).
3. Index Creation:
   • If a candidate index shows consistent performance improvement, it’s created as a real index.
4. Index Validation:
   • Newly created indexes are validated over time. If they are not used or degrade performance, they are dropped automatically.
5. Rebuild & Drop:
   • The system can rebuild indexes if fragmentation is detected, and drop unused ones to save space.

Enabling Automatic Indexing in Oracle 19c

Automatic Indexing is disabled by default. You can enable it with the following steps:

1. Enable the Feature

sql>EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’, ‘IMPLEMENT’);

Modes available:

• 'OFF' – Disables automatic indexing
• 'REPORT ONLY' – Identifies candidate indexes but does not create them
• 'IMPLEMENT' – Fully enables automatic indexing

2. Check Current Status

sql>SELECT * FROM DBA_AUTO_INDEX_CONFIG;

3. View Index Activity

— View all automatically created indexes
SELECT INDEX_NAME, TABLE_NAME, STATUS, AUTO FROM DBA_INDEXES WHERE AUTO = ‘YES’;

— View activity log
SELECT * FROM DBA_AUTO_INDEX_EXECUTIONS ORDER BY EXECUTION_START DESC;

4. Control Storage and Schema

EXEC DBMS_AUTO_INDEX.CONFIGURE(‘DEFAULT_TABLESPACE’, ‘USERS’);
EXEC DBMS_AUTO_INDEX.CONFIGURE(‘INCLUDE’, ‘HR’);

Requirements

• Oracle 19c Enterprise Edition
• Oracle Tuning Pack License
• Compatible parameter ≥ 19.1.0