Automatic Indexing in Action-Indexes

Now that you have some background with the automatic indexing feature, let’s enable it and see how it works:
$ sqlplus system/foo@PDB1
SQL> exec dbms_auto_index.configure(‘AUTO_INDEX_MODE’,’IMPLEMENT’); PL/SQL procedure successfully completed.

Next, I’ll create a table to test with:
$ sqlplus eoda/foo@PDB1
SQL> create table d (d varchar2(30));Table created.

Now I’ll insert some random number data into this table:
SQL> insert into d(d)select trunc(dbms_random.value(1,100000))
from dualconnect by level <= 1000000;1000000 rows created.

Next, I’ll create a PL/SQL loop that loops and selects from the table. The idea is to create a SQL statement that currently is not using an index, but perhaps could have its performance improved if there was an index. For example:
SQL> declarei integer;j integer; begin
for l_counter in 1..100000 loopbeginselect trunc(dbms_random.value(1,100000)) into i from dual; select distinct(d) into j from d where d = i;exceptionwhen no_data_found then null;end; end loop; end;/

In another session, the following SQL will report on any automatic indexing activities:
SQL> set long 1000000 pagesize 0
SQL> — Default TEXT report for the last 24 hours.
SQL> select dbms_auto_index.report_activity() from dual;
GENERAL INFORMATION

Activity start : 09-JUL-2021 16:07:14
Activity end : 10-JUL-2021 16:07:14
Executions completed : 1
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 : 1x

SUMMARY (MANUAL INDEXES)

Unused indexes : 0
Space used : 0 B
Unusable indexes : 0

From the prior output, no automatic indexing has taken place yet. This means the Oracle jobs that manage auto indexing haven’t executed. The automatic indexing feature is managed in the background by Oracle jobs. You can view those jobs via
SQL> select task_id, task_name, advisor_name from dba_advisor_tasks
where owner=’SYS’and task_name like ‘%AI%’order by task_id;
TASK_ID TASK_NAME ADVISOR_NAME
3 SYS_AI_SPM_EVOLVE_TASK SPM Evolve Advisor
4 SYS_AI_VERIFY_TASK SQL Performance Analyzer

After waiting a few minutes and giving the automatic indexing a chance to evaluate the system, I’ll run the report again to see if any candidate indexes have been identified. For example:
SQL> select dbms_auto_index.report_activity() from dual;SUMMARY (AUTO INDEXES)
…
Index candidates : 6
Indexes created : 0
…

From the report output, some candidate indexes have been identified in this database. We can view those candidate indexes via the following query:
SQL> select index_owner, table_name, index_name, column_name from dba_ind_columns
where index_name like ‘SYS_AI%’and table_name=’D’;

And we see that an index on the table D and column D is a candidate for indexing:
INDEX_OWNE TABLE_NAME INDEX_NAME COLUMN_NAME
EODA D SYS_AI_gc454q9xmxbqv D

Digging into this a little further, I’ll query the DBA_INDEXES view to see if any auto indexes have been created:
SQL> select index_name, visibilityfrom dba_indexeswhere index_name like ‘SYS_AI%’and table_name = ‘D’;

I can see one auto index has been created, and it’s currently in the INVISIBLE state. This indicates the auto indexing has created an index and is now evaluating it to see if it will improve performance:
INDEX_NAME VISIBILITY
SYS_AI_gc454q9xmxbqv INVISIBLE

After waiting several minutes, and querying the DBA_INDEXES view again, it shows that the helpful index has been made visible:
INDEX_NAME VISIBILITY
SYS_AI_gc454q9xmxbqv VISIBLE

We can verify that this index is in use by generating an execution plan. For example:
SQL> set autotrace trace explain;
SQL> select d from d where d = 100;

This is a simple example, but you can see the basic idea here. Automatic indexing evaluates the system and identifies indexes that might improve performance. The indexes are initially created as invisible, and then afterward, if the index is deemed to improve performance, the index is then made visible.