Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In this article, we will introduce the DBMS_AUTO_INDEX feature, which acts with the autonomous database policy first on the database side and then in the operating system.
In every database, we encounter the concept of index in faster access to data or performance processes. Index operations can be advantageous as well as disadvantageous.
Since indexes take up space on the physical disk, they can bring us an extra maintenance burden in addition to occupying unnecessary space in case of rude use.
How does the Oracle Automatic Indexing (DBMS_AUTO_INDEX) feature work?
First of all, “Candidate Indexes”, which are defined as candidate indexes, are determined according to the usage status of the tables and columns.
Tables with current statistics are prepared for index operations. Tables with old statistical information will not be indexed automatically.
For candidate indexes, invisible indexes are created with the prefix SYS_AI. Since the Invisible index is created, it is not used in sql statements in the first place.
SQL performances are tested bilaterally. If SQL queries improve with invisible indexes, visible indexes will be entered. In this way, automatically generated indexes become used in SQL queries.
If the indexes that are created as invisible do not provide sufficient performance in sql queries, they are marked as unusable index and the sql query is blacklisted.
The unusable index is then deleted and the blacklisted sql query is prevented from being used in the automatic index again.
How to activate Oracle Automatic Indexing (DBMS_AUTO_INDEX) feature?
Auto index feature is used by processing DBMS_AUTO_INDEX procedure and can be activated after configuring AUTO_INDEX_MODE.
AUTO_INDEX_MODE can take the following values.
IMPLEMENT : Activates the automatic index feature. Invisible indexes become visible indexes. It can be used by the optimizer.
REPORT ONLY : Automatic index opens, but candidate indexes remain invisible.
OFF : Automatic index is turned off.
1 2 3 | EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT'); EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY'); EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF'); |
By default, automatic indexes are located on the tablespace where the table is located. If you want to add a different tablespace for the indexes that will be created automatically.
1 2 3 | CREATE TABLESPACE AUTO_INDEXES_TS DATAFILE SIZE 1024M AUTOEXTEND ON NEXT 200M; EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE','AUTO_INDEXES_TS'); |
To reset to default setting;
1 | EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE',NULL); |
When the Oracle Auto-index feature is activated, it includes all schemas as standard.
By using the AUTO_INDEX_SCHEMA property, you can specify the schemas to be added or removed with the TRUE parameter.
1 | EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'KADAYIF', allow => TRUE); |
To see automatic index information
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | /* Auto İndex Script B.PARLAYAN */ SELECT con_id, parameter_name, parameter_value FROM cdb_auto_index_config ORDER BY 1, 2; Script Output 0 AUTO_INDEX_COMPRESSION OFF 0 AUTO_INDEX_DEFAULT_TABLESPACE 0 AUTO_INDEX_MODE OFF 0 AUTO_INDEX_REPORT_RETENTION 31 0 AUTO_INDEX_RETENTION_FOR_AUTO 373 0 AUTO_INDEX_RETENTION_FOR_MANUAL 0 AUTO_INDEX_SCHEMA 0 AUTO_INDEX_SPACE_BUDGET 50 |
If you want to observe the activities related to the automatic index, you can use the query below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | SELECT DBMS_AUTO_INDEX.report_activity() FROM dual; GENERAL INFORMATION ------------------------------------------------------------------------------- Activity start : 23-JAN-2020 02:44:55 Activity end : 23-JAN-2020 02:44:55 Executions completed : 0 Executions interrupted : 0 Executions with fatal error : 0 ------------------------------------------------------------------------------- SUMMARY (AUTO INDEXES) ------------------------------------------------------------------------------- Index candidates : 0 Indexes created : 0 Space used : 0 B Indexes dropped : 0 SQL statements verified : 0 SQL statements improved : 0 SQL plan baselines created : 0 Overall improvement factor : 0x ------------------------------------------------------------------------------- SUMMARY (MANUAL INDEXES) ------------------------------------------------------------------------------- Unused indexes : 0 Space used : 0 B Unusable indexes : 0 ------------------------------------------------------------------------------- ERRORS --------------------------------------------------------------------------------------------- No errors found. --------------------------------------------------------------------------------------------- |
