Managing Automatic Indexing-Indexes

Automatic indexing is managed via the internal DBMS_AUTO_INDEX PL/SQL package. The default mode of automatic indexing is REPORT ONLY. In this mode, automatic indexing is on, but any indexes it creates are invisible (meaning the indexes won’t be used by the optimizer).

You can enable/disable indexing at the root container level or at the pluggable database level. In other words, it is possible to enable auto indexing for activities in the root container, but not in a pluggable database (and vice versa). You can view the current settings of automatic indexing as follows (since I’m connecting to a pluggable database, the following only shows the PDB level settings):

$ sqlplus eoda/foo@PDB1
SQL> select con_id, parameter_name, parameter_value
from cdb_auto_index_configorder by 1, 2;
CON_ID PARAMETER_NAME PARAMETER_VALUE
3 AUTO_INDEX_COMPRESSION OFF
3 AUTO_INDEX_DEFAULT_TABLESPACE
3 AUTO_INDEX_MODE REPORT ONLY
3 AUTO_INDEX_REPORT_RETENTION 31
3 AUTO_INDEX_RETENTION_FOR_AUTO 373
3 AUTO_INDEX_RETENTION_FOR_MANUAL
3 AUTO_INDEX_SCHEMA
3 AUTO_INDEX_SPACE_BUDGET 50

You can modify any of the prior listed parameters via the CONFIGURE procedure of DBMS_AUTO_INDEX. For example, you enable (turn it on) the automatic indexing feature as follows:
SQL> exec dbms_auto_index.configure(‘AUTO_INDEX_MODE’,’IMPLEMENT’);

And similarly, you can disable (turn off) automatic indexing as follows:
SQL> exec dbms_auto_index.configure(‘AUTO_INDEX_MODE’,’OFF’);

Once automatic indexing has been enabled, all schemas are candidates for having indexes automatically managed. You can modify this via the CONFIGURE procedure. For example, to build an inclusion list, do so as follows:
SQL> exec dbms_auto_index.configure(‘AUTO_INDEX_SCHEMA’, ‘EODA’, allow => TRUE);

Now if we run the query displaying configuration parameters, we’ll see the inclusionlist:
SQL> CON_ID PARAMETER_NAME PARAMETER_VALUE
3 AUTO_INDEX_SCHEMA schema IN (EODA)

Similarly, you can build an exclusion list:
SQL> exec dbms_auto_index.configure(‘AUTO_INDEX_SCHEMA’, ‘EODA’, allow => FALSE);
SQL> select con_id, parameter_name, parameter_value from cdb_auto_index_config
where parameter_name=’AUTO_INDEX_SCHEMA’;CON_ID PARAMETER_NAME PARAMETER_VALUE3 AUTO_INDEX_SCHEMA schema NOT IN (EODA)

To set the AUTO_INDEX_SCHEMA value back to the default, pass in a NULL to the procedure as follows:
SQL> exec dbms_auto_index.configure(‘AUTO_INDEX_SCHEMA’, NULL, allow => FALSE);

Some features of automatic indexing should only be used after you’ve thoroughly tested them. For example, there’s a feature to drop all of the secondary indexes that exist in the database or in a particular schema. Secondary indexes are defined as those not used to enforce constraints. Be very cautious with this feature!

Having said that, this next bit of code will drop all secondary indexes in the database. If you’re connected to a pluggable database, it will only drop the secondary indexes in that pluggable database, for example:
$ sqlplus system/foo@PDB1
SQL> exec dbms_auto_index.drop_secondary_indexes;

If your database contains a great number of indexes (most do), then the prior command will take several minutes to run.

To drop indexes from a specific schema, do as follows:
SQL> exec dbms_auto_index.drop_secondary_indexes(‘EODA’,NULL);

To drop secondary indexes on a specific table, do as follows:
SQL> exec dbms_auto_index.drop_secondary_indexes(‘EODA’,’T’);

Needless to say, the prior dropping of indexes should be done very carefully, and only after you’re sure it will do what you think it will do.