Automatic Indexing - Part I

Auto Indexing (AI) is a new feature introduced in Oracle database 19c. the intent of Oracle here is to make the configuration of automatic indexing as simple as possible, with the few levers of DBA to potentially stuff up as much as possible. The ultimate goal would be to have a switch that just turns the feature on and that all necessary indexes then simply be created/modified/dropped as required. it is not quite there yet, but it will no doubt to get closer there in the next release.
 
By default automatic indexing is turned OFF, to turn on these capabilities we have to make use of the methods (procedures/functions) available in the package DBMS_AUTO_INDEX.
 
demo@PDB19> exec dbms_auto_index.configure('AUTO_INDEX_MODE','IMPLEMENT');
 
PL/SQL procedure successfully completed.
 
that’s it, AI is now enabled and for every 15 minutes the background task (SYS_AUTO_INDEX_TASK) will kickoff and automatically create/drop/replace any database index as necessary.
 
Another option is to enable AI in “REPORT ONLY” mode:
 
demo@PDB19> exec dbms_auto_index.configure('AUTO_INDEX_MODE','REPORT ONLY');
 
PL/SQL procedure successfully completed.
 
This will create new indexes as “INVISIBLE” indexes, which are not considered by default by the CBO, the intent here is that DBA can investigate the newly created automatic indexes and decide whether  turning on this feature for real would be a good idea. Need to exercise some caution with this option though, as the limited options, regarding how to subsequently administer the created invisible automatic index can be problematic, will discuss about them in future blog posts.
 
To turn of AI simply set AUTO_INDEX_MODE to OFF.
 
demo@PDB19> exec dbms_auto_index.configure('AUTO_INDEX_MODE','OFF');
 
PL/SQL procedure successfully completed.
 
When set to OFF, new AI will no longer be created, but all existing AI will still be both visible and valid and available to CBO for continued use.
 
By default AI considers all tables in “user created” schemas, however this can be controlled by AUTO_INDEX_SCHEMA option within the dbms_auto_index.configure procedure. You can control which schema to either explicitly include or exclude from AI considerations.
 
To add AI_DEMO schema to the “inclusion” list of schemas, we can run the following
 
demo@PDB19> exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA','AI_DEMO');
 
PL/SQL procedure successfully completed.
 
To add AI_DEMO schema to the “exclusion” list of schemas, we can run the following
 
demo@PDB19> exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA','AI_DEMO',FALSE);
 
PL/SQL procedure successfully completed.
 
To remove AI_DEMO schema from whichever list it belongs, we can run the following
 
demo@PDB19> exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA','AI_DEMO',NULL);
 
PL/SQL procedure successfully completed.
 
To remove all the schema from the “inclusion” list of schemas, we can run the following
 
demo@PDB19> exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA',NULL,TRUE);
 
PL/SQL procedure successfully completed.
 
We can also configure a tablespace to be the tablespace in which all AI are to now be created by running the following
 
demo@PDB19> exec dbms_auto_index.configure('AUTO_INDEX_DEFAULT_TABLESPACE','TS_INDEX_DEMO');
 
PL/SQL procedure successfully completed.
 
The tablespace TS_INDEX_DEMO is now the location for all newly created AI.
 
We can also control how much of the configured AI tablespace is to be reserved for use by automatic indexes (default is 50%).
 
demo@PDB19> exec dbms_auto_index.configure('AUTO_INDEX_SPACE_BUDGET','100');
 
PL/SQL procedure successfully completed.
 
We can also control the number of days (the default is 373 days) in which if an AI has been deemed not to have been used it will automatically dropped. The following command
 
demo@PDB19> exec dbms_auto_index.configure('AUTO_INDEX_RETENTION_FOR_AUTO','60');
 
PL/SQL procedure successfully completed.
 
Will automatically drop any AI that is deemed not to have been used in the last 60 days.
 
Note: Oracle uses the new index tracking feature introduced in Oracle 12.2 to determine if an index has/has not been used, which has limitation that could potentially result in AI that has been “lightly” used during the retention period being dropped.
 
Similar retention configuration can be implemented for manually created indexes using the following command.
 
demo@PDB19> exec dbms_auto_index.configure('AUTO_INDEX_RETENTION_FOR_MANUAL','60');
 
PL/SQL procedure successfully completed.
 
Will automatically drop any manually created index that is deemed not to have been used in the last 60 days.
 
AI logs on which automatic indexing reports are based are only retained by default for just 373 days, these reports are critical for understanding what AI featuring is doing within the database. Incase if you like to change it should be like this
 
demo@PDB19> exec dbms_auto_index.configure('AUTO_INDEX_REPORT_RETENTION','60');
 
PL/SQL procedure successfully completed.
 
The final configuration option controls whether or not advanced compression is used for AI, the default is advanced compression is disabled, but that can be changed as follows
 
demo@PDB19> exec dbms_auto_index.configure('AUTO_INDEX_COMPRESSION','ON');
 
PL/SQL procedure successfully completed.
 
Finally, the documented DBA_AUTO_INDEX_CONFIG view can be used to view the current settings for all the configuration options.
 
demo@PDB19> select parameter_name ,
  2      parameter_value
  3  from dba_auto_index_config;
 
PARAMETER_NAME                      PARAMETER_VALUE
----------------------------------- ------------------------------
AUTO_INDEX_COMPRESSION              ON
AUTO_INDEX_DEFAULT_TABLESPACE       TS_INDEX_DEMO
AUTO_INDEX_MODE                     IMPLEMENT
AUTO_INDEX_REPORT_RETENTION         373
AUTO_INDEX_RETENTION_FOR_AUTO       373
AUTO_INDEX_RETENTION_FOR_MANUAL
AUTO_INDEX_SCHEMA                   schema IN (AI_DEMO)
AUTO_INDEX_SPACE_BUDGET             100
 
8 rows selected.
 
demo@PDB19>
 

Importantly, AI is only available on Exadata platforms and  on Oracle cloud environments, It is not available on standard on-premises Oracle 19c deployments, attempts to turn that feature on where not supported will only result in  error.

 

demo@XEPDB1> exec dbms_auto_index.configure('AUTO_INDEX_MODE','IMPLEMENT');
BEGIN dbms_auto_index.configure('AUTO_INDEX_MODE','IMPLEMENT'); END;
 
*
ERROR at line 1:
ORA-40216: feature not supported
ORA-06512: at "SYS.DBMS_AUTO_INDEX", line 353
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 10326
ORA-06512: at "SYS.DBMS_AUTO_INDEX", line 340
ORA-06512: at line 1