The automatic indexing feature automates the indexing in an Oracle database. Automatic indexing automatically creates, rebuilds, and drops indexes in a database based on the changes in application workload, thus improving database performance. The automatically managed indexes are known as auto indexes.
With 19c and Oracle Autonomous Database, things have changed drastically. Automatic Indexing provides what is required in the database systems.It continuously optimizes the database workload, and makes it stable & to ensure solid performance and almost no human interaction.
Configuration of automatic Indexing in 19c :
Enable automatic indexing in a database and creates any new auto indexes as visible indexes, so that they can be used in SQL statements:
EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’,’IMPLEMENT’);
Enable automatic indexing in a database, but creates any new auto indexes as invisible indexes, so that they cannot be used in SQL statements:
EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’,’REPORT ONLY’);
Disable automatic indexing in a database, so that no new auto indexes are created, and the existing auto indexes are disabled:
EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’,’OFF’);
Set retention period for unused auto indexes to 90 days:
EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_RETENTION_FOR_AUTO’, ’90’);
Set retention period for unused non-auto indexes to 60 days:
EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_RETENTION_FOR_MANUAL’, ’60’);
Define tablespace TBSAUTO to store auto indexes:
EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_DEFAULT_TABLESPACE’, ‘TBSAUTO’);
Allocates 5% of the tablespace for auto indexes:
EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_SPACE_BUDGET’, ‘5’);
