Automatic Indexing in Oracle 19c Autonomous Database

With Oracle Database 19c, The Automatic Indexing feature automates the index management by allowing Oracle Database to automatically create, rebuild and drop indexes based on application workload. Oracle Database runs the automatic indexing process in the background, analyzes application workload, and creates new indexes and drops redundant and extra ones.


The automatic indexing process runs in background every 15 minutes and performs the following operations:
1. Identifies auto index candidates based on the usage of table columns in SQL statements.
2. Creates invisible auto indexes for the auto index candidates
3. The invisible auto indexes are validated against SQL statements.
     a. Index are marked visible If the performance is improved
     b. Indexes are configured as unusable, If performance of SQL statements is not improved.
     c. If performance degrades for some SQLs, the indexes are marked visible except for the these SQLs (blacklisted)
4. The auto indexes that are not used for a long period are deleted (373 days by default).


Note:
1. Tables with stale statistics are not considered for auto indexing.
2. Blacklisted SQL statements are not allowed to use auto indexes in future.

Configuring Automatic Indexing
1. Enable Oracle to Create Automatic Indexes in Visible Mode
    EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');

2. Enable Oracle to Create Automatic Indexes but in Invisible Mode
    EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');

3. Disables automatic indexing in a database
    EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');

4. Add Schema in exclusion list for Auto Indexing
    EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'SH', FALSE);

5. Remove Schema from exclusion list for Auto Indexing
    EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'SH', NULL);

Also we can use hints in SQL statements to control if auto indexes should be used
- /*+ USE_AUTO_INDEXES */
- /*+ NO_USE_AUTO_INDEXES */

Related Links -
- Top 18 features of Oracle 18c
- Top 15 new features of Oracle Database 12.2 for developers
- Oracle Database 12c New Features for Developers
- JSON in Oracle Database with Examples
- Oracle Cloud Performance Benchmark by Accenture
- Oracle 18c Database - Autonomous Database Cloud
- Oracle Cloud - Introducing New Always Free Services