Wednesday , May 29 2024

Automatic Indexing In Oracle 19c

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.

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.

To reset to default setting;

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.

To see automatic index information

If you want to observe the activities related to the automatic index, you can use the query below.


About Buğra PARLAYAN

Burgra Parlayan is an experienced Database and Weblogic Administrator. After completing his technical / relevant training he has got involved with a serious amount of projects. He successfully managed database upgrade, database migration, database performance tuning projects for various public institutions.Currently he has been employed by one of the leading financial institutions called Turkiye Hayat & Emeklilik as responsible administrator for Oracle Database and Oracle Middleware. He has been sharing his experience and knowledge by face to face training, personal blog and various social networking accounts to support the Oracle ecosystem continuously since 2010.

Leave a Reply

Your email address will not be published. Required fields are marked *