Oracle : Auto index feature

Careful, I am endorsing any feature. Use judiciously. 

DOC: https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-indexes.html#GUID-FF3F7EF5-9468-48BC-8990-8189C1CBFD12

Auto Indexing feature

--create/make it available

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

--create/keep invisible

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

--Feature off

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

***AUTO_INDEX_SCHEMA configuration setting to specify schemas that can use auto indexes

-- exclusion list

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'MYSCHEMA', FALSE);

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'YOURSCHEMA', FALSE);

--remove from exclusion list i.e. include

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'HR', NULL);

--Clear exclude list

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', NULL, TRUE);

***Retition for unused indexes

AUTO_INDEX_RETENTION_FOR_AUTO

--retain for 30days

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

--default retention 373 days

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

---Warning/Alert Applicable for Manually created indexes(Use judgement)

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

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

**Auto index logs/tracking

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_REPORT_RETENTION', '160');

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_REPORT_RETENTION', NULL);        --default 373

**Specify tablespace for auto index

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE', 'TBS_AUTO');

**Allocation for auto indexes

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SPACE_BUDGET', '15');   --15%

**Compression on off

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

###**Reporting on auto index

REPORT_ACTIVITY and REPORT_LAST_ACTIVITY functions of the DBMS_AUTO_INDEX package

declare

  report clob := null;

begin

  report := DBMS_AUTO_INDEX.REPORT_ACTIVITY();

end;

declare

  report clob := null;

begin

  report := DBMS_AUTO_INDEX.REPORT_ACTIVITY(

              activity_start => TO_TIMESTAMP('2018-11-01', 'YYYY-MM-DD'),

              activity_end   => TO_TIMESTAMP('2018-12-01', 'YYYY-MM-DD'),

              type           => 'HTML',

              section        => 'SUMMARY',

              level          => 'BASIC');

end;