Automatic Indexing. New Oracle Functionality

Hello everyone. Today we are going to talk about a new functionality of Oracle 19c that we think can be very useful. The functionality is called Automatic Indexing and is one of the most important advances that have occurred in recent years.

What is Automatic Indexing?

Basically, the database analyzes the current load of the same and is able to identify possible indices that can improve it autonomously. It creates them in a way that is not visible to users (invisible) and evaluates if their performance is as expected, if so, it makes them visible to applications and leaves them as permanent.

The The following period in the evaluation is as follows:

automatic indexing oracle 19c gpsos

It’s a process that runs every 15 minutes by default in background and takes care of the maintenance of the Indexes. The functionality is managed with the DBMS_AUTO_INDEX package and has the following peculiarities:

  • Automatic candidate indexes are created as default unviable indexes.
  • If the performance of SQL statements does not improve with the new index, the index is marked as UNUSABLE and the SQL statements are marked in a blacklist.
  • Auto indexes cannot be used for an SQL statement that is executed for the first time in the database.
  • Auto indexes are created as single (one column), concatenated indexes, or featured indexes, and all use advanced low compression.
  • Unused auto indexes are deleted after 373 days by default.
  • Indexes that are not automatic but are not used (those created manually) are never deleted by the automatic indexing process (but can be configured as well).

The automatic indexing process can be deactivated at any time or left only in report mode, being created only in invisible mode and without being used by the manager. This is done with the commands:

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

View process activity

To see the activity that the process has carried out, use the report_activity of the package itself:

SELECT DBMS_AUTO_INDEX.report_activity() FROM dual;

As we have seen the functionality is very interesting and in the sites we have tested it usually work quite well.

We hope you like the entrance and until the next one. If you don’t want to miss any, sign up for our monthly newsletter . With only one email per month you will be informed of all our publications.

More information: https://blogs.oracle.com/oraclemagazine/autonomous-indexing

Comments are closed.