Automatic Indexing

Introduction

We all know that Index structures are vital for optimal database performance, but often we create indexes and never revisit them, even after our data, application or table structures change. To maintain peak performance, it is crucial to keep the indexes up-to-date in response to changes in the database usage patterns. Neglecting to update indexes in line with these changes can significantly harm database performance.

Therefore, Oracle have introduced a great new feature in 19c. Its called automatic indexing. Not only does it monitor your database and automatically create indexes, it also automates other index management tasks. For example, rebuilding and dropping indexes based on changes in your changing real-world database environment.

key Points

  • Indexes created by the automatic index feature are known as auto indexes.
  • New automatic indexes are identified by a process that runs every 15 minutes. This process identifies indexes based on the usage of table columns accessed in SQL statements.
  • Once identified, indexes are created invisible and unusable so that they cannot be seen or used by SQL statements
  • Oracle then selects a sample of SQL statements and test them against the new indexes to validate any improvements.
  • Any indexes shown to improve SQL performance will be made visible and usable.

Important Notes

  • If an index shows that some SQL statements benefit, but some perform worse, then oracle will create a SQL baseline to prevent the index from being used for the poor performing SQL statements.
  • Table statistics must be up to date. Tables with stale or no statistics are removed as candidates for automatic indexing.
  • Automatic Indexes may have one or more columns
  • Automatic Indexes can contain virtual columns
  • Automatic indexes can be added to Partitioned and non-partitioned tables
  • If SQL statements are not improved using automatic indexes then they remain invisible.
  • Unused automatic indexes are dropped after 373 days.

PLSQL Examples for Using and Configuring Automatic Indexes.

The main package for controlling automatic indexing is

  • DBMS_AUTO_INDEX

Example A - Turning Automatic Indexes On or Off.
The following statements will turn automatic index on or off.

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT'); -- Turn in automatic indexing
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');  -- Turn off automatic indexing

Notes
Be aware, turning automatic indexing on using this command will create indexes that are visible and usable.
Be aware. turning automatic indexing off using this command will leave current indexes intact

Example B - Turning Automatic Indexes On, but in Reporting Mode.
The following statement will turn automatic indexing on but in reporting mode. That is, the indexes will be created invisible. This allows the DBA to review the indexes before making them visible.

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');

Example C - Setting an Exclusion List
Schemas can be placed on an exclusion list using this package. This lets a schema either make use off, or forbids a schema from making use of, automatic index.

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'MYSCHEMA', FALSE);  -- Add MYSCHEMA to the exclusion list.  i.e. Forbid MYSCHEMA form using automatic indexes.
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'MYSCHEMA', NULL); -- Remove MYSCHEMA form the exclusion list.  i.e. Allow MYSCHAMA to use automatic indexes.

Example D - Setting the Deletion Period for Unused Indexes.
By default, unused automatic indexes are deleted after 373 days. This value can be changed using the following command

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', '180');

It is very important to note, that unused manually created indexes can also be set for deletion. (By default they are never deleted).

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_MANUAL', '60');

Example E - Setting Compression for Auto Indexes
The following statement will switch on advanced index compressions for automatic indexes created

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_COMPRESSION','ON');

Published 7th March 2023

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License