Automatic Indexing: 3 Possible States Of Newly Created Automatic Indexes Part II (“Because You’re Young”)

In a previous post, I discussed how there can be three different states of newly created Automatic Indexes, based on the predicate selectivities of the SQLs that generated the indexes.

In this post, I’m going to highlight new behaviour I’ve noticed with Oracle 21c that results in the three different states of newly created Automatic Indexes based on the number of times of new SQL statements are executed.

To demonstrate, I’m going to create 3 identical tables and populate them with identical data:

SQL> create table bowie1 (id number, code number, name varchar2(42));
SQL> create table bowie2 (id number, code number, name varchar2(42));
SQL> create table bowie3 (id number, code number, name varchar2(42));

SQL> insert into bowie1 select rownum, mod(rownum, 1000000)+1, 'David Bowie’ from dual connect by level <= 10000000;
SQL> insert into bowie2 select rownum, mod(rownum, 1000000)+1, 'David Bowie’ from dual connect by level <= 10000000;
SQL> insert into bowie3 select rownum, mod(rownum, 1000000)+1, 'David Bowie’ from dual connect by level <= 10000000;

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE1');
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE2');
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE3');

I then run the following queries in the following manner.

I run this query based on a CODE equality predicate on the BOWIE1 table just one time only:

select * from bowie1 where code=42;

I run the same query on the BOWIE2 table a few times (definitely more than once, say 5 times):

select * from bowie2 where code=42;

I run the following queries on the BOWIE3 table multiple times (say 10 or more times):

select * from bowie3 where code=42;
select * from bowie3 where code=24;
select * from bowie3 where code=4242;
select * from bowie3 where code=4444;

 

I then await the Automatic Indexing process and examine the resultant generated Automatic Indexes:

SQL> select table_name, index_name, auto, visibility, status, num_rows, leaf_blocks
from user_indexes where table_name in ('BOWIE1','BOWIE2', 'BOWIE3');

TABLE_NAME INDEX_NAME           AUT VISIBILIT STATUS     NUM_ROWS LEAF_BLOCKS
---------- -------------------- --- --------- -------- ---------- -----------
BOWIE1     SYS_AI_1c6k1qpybh694 YES INVISIBLE UNUSABLE   10000000       23058
BOWIE2     SYS_AI_8v1sh0j56g4hs YES INVISIBLE VALID      10000000       16891
BOWIE3     SYS_AI_g6tta1zrmak4d YES VISIBLE   VALID      10000000       16891

 

We notice we have each of our possible 3 Automatic Index states.

The Automatic Index on the BOWIE1 table with a query that was “only executed once” has an INVISIBLE/UNUSABLE index. Where previously Automatic Indexing would have simply generated a VISIBLE/VALID index even on a query that was only executed once, in more current releases, the logic seems to be if a query is very very infrequently executed, it will not be enough on it’s own to generate a usable Automatic Index.

The Automatic Index on the BOWIE2 table with a single query that was only executed a “very few number of times” (5 in this example) has an INVISIBLE/VALID index. Where previously again Automatic Indexing would have simply generated a VISIBLE/VALID index, in more current releases, the logic seems to be if a query is executed only a few times, it’s close to being useful, but not quite to generate a Visible index. Having it as a Visible index is perhaps something to consider in the near future, if the useful load increases sufficiently.

The Automatic Index on the BOWIE3 table with a query that runs “many times” and/or with multiple queries on the column has a VISIBLE/VALID index. The logic seems to be if the index is used sufficiently to justify its creation, the index is actually created in a state in which it can be generally subsequently used.

So there are a number of possible scenarios now in which an Automatic Index is created in this “in-between” INVISIBLE/VALID state.

It would perhaps be a nice addition if we had the ability to alter the Visibility state of an Automatic Index. But I guess the whole idea behind Automatic Indexing is that we shouldn’t need to worry about such things…