Automatic Indexing

A Smarter Way to Index

Think of a massive library where the librarian constantly watches which books are checked out. To make things faster, the librarian creates new, specialized catalogs for the most popular topics. That's essentially what Automatic Indexing does for your database. Instead of you manually figuring out which indexes to create, Oracle Database 19c does it for you.

This feature continuously monitors how your data is being accessed. When it identifies a query that could be sped up, it steps in. It analyzes your workload, identifies candidate indexes, and then verifies if they actually improve performance before making them permanent. It’s a hands-off approach designed to optimize your database without requiring constant manual tuning.

Automatic Indexing: Oracle 19c introduces automatic indexing, which enhances query performance by automatically creating and managing indexes based on the workload.

Enabling and Configuring

You can turn on Automatic Indexing at the system level. The feature has two primary modes. You can either let it run reports to suggest changes, or you can allow it to implement those changes automatically.

To enable it, you use the DBMS_AUTO_INDEX.CONFIGURE procedure. The key parameter is AUTO_INDEX_MODE.

Mode	Description
`REPORT ONLY`	Identifies and reports on candidate indexes but does not create them.
`IMPLEMENT`	Creates, tests, and enables new indexes automatically if they help.
`OFF`	Disables the feature entirely.

For example, to set the database to start implementing automatic indexes, you would run the following command:

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

Starting with REPORT ONLY is a safe way to see what the feature would do without making any actual changes to your database schema.

Watching It Work

After enabling Automatic Indexing, you'll want to see what it's up to. Oracle provides several data dictionary views and reports to monitor its activity. The main view for checking the configuration is DBA_AUTO_INDEX_CONFIG.

SELECT PARAMETER_NAME, PARAMETER_VALUE
FROM   DBA_AUTO_INDEX_CONFIG;

To see a detailed log of its actions, you can generate a report. The DBMS_AUTO_INDEX.REPORT_ACTIVITY function gives you a complete overview of what happened during a specific time frame. You can see which indexes were created, which were tested, and the performance gains.

SET LONG 1000000
SET PAGESIZE 50000
SET LINESIZE 200
SELECT DBMS_AUTO_INDEX.REPORT_ACTIVITY(
         report_type => 'HTML',
         section     => 'ALL'
       ) AS report
FROM   dual;

This will produce an HTML report with summaries, index details, and verification results.

Interpreting the Decisions

Understanding why an automatic index was created is key to trusting the system. The process follows a logical, multi-step lifecycle to ensure only beneficial indexes are added.

The "Verify" step is crucial. By creating indexes as invisible first, Oracle tests them in the real world without impacting other queries that aren't part of the test. Only after confirming a net performance gain does the index become visible and available for general use. If the index doesn't help or, worse, slows things down, it's automatically removed. This built-in safety check prevents the system from cluttering your database with useless indexes.

Best Practices

While Automatic Indexing is powerful, a little guidance helps it perform at its best.

Start in REPORT ONLY mode. This lets you review the recommendations before giving the system full control. It's a great way to build confidence and ensure its suggestions align with your understanding of the application.

Define a retention period. By default, unused automatic indexes are kept for about a year (AUTO_INDEX_RETENTION_FOR_AUTO is 373 days). You can configure this to match your needs, ensuring that indexes for quarterly or annual reports aren't dropped prematurely.

Regularly review the reports. Even though the process is automatic, periodic checks are smart. Use the activity reports to understand what changes are being made and confirm they are having the desired positive impact on performance.

By following these practices, you can leverage Automatic Indexing to maintain an optimized database with minimal effort, freeing you to focus on other critical tasks.

Mastering Oracle Database 19c Advanced Features