Richard Foote's Oracle Blog

Oracle 19c Automatic Indexing: The 3 Possible States Of Newly Created Automatic Indexes (“Don’t Sit Down”) August 24, 2021

As I discussed way back in February 2021 (doesn’t time fly!!), I discussed some oddity cases in which Automatic Indexes were being created in an Invisible/Valid state. At the time, I described it as unexpected behaviour as this wasn’t documented and seemed an odd outcome, one which I had only expected to find when Automatic Indexing was set in “REPORT ONLY” mode.

After further research and discussions with folks within Oracle, Automatic Indexes created in this state is indeed entirely expected, albeit in relatively rare scenarios. So I thought I’ll discuss the 3 possible states in which an Automatic Index can be created and explore things further in future blog posts.

The follow demo illustrates the 3 different states in which Automatic Indexes can be created.

I start by creating a table with 3 columns of note:

• CODE1 which is highly selective and very likely to be used by the CBO if indexed
• CODE2 which is relatively selective BUT likely NOT quite enough so to be used by the CBO if indexed
• CODE3 which is very unselective and almost certainly won’t be used by the CBO if indexed

SQL> create table david_bowie (id number, code1 number, code2 number, code3 number, name varchar2(42));

Table created.

SQL> insert into david_bowie select rownum, mod(rownum, 1000000)+1, mod(rownum, 5000)+1, mod(rownum, 100)+1, 'THE RISE AND FALL OF ZIGGY STARDUST' from dual connect by level >=10000000;

10000000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'DAVID_BOWIE');

PL/SQL procedure successfully completed.

Note that in an Autonomous Database, these columns will all now have histograms (as previously discussed):

SQL> select column_name, num_distinct, density, histogram from dba_tab_columns where table_name='DAVID_BOWIE';

COLUMN_NAME          NUM_DISTINCT    DENSITY HISTOGRAM
-------------------- ------------ ---------- ---------------
ID                        9705425          0 HYBRID
CODE1                      971092    .000001 HYBRID
CODE2                        4835    .000052 HYBRID
CODE3                         100  .00000005 FREQUENCY
NAME                            1 4.9460E-08 FREQUENCY

I’ll now run the following simple queries a number of times, using predicates on each of the 3 columns:

SQL> select * from david_bowie where code1=42;

10 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1390211489

-----------------------------------------------------------------------------------------
| Id | Operation                 | Name        | Rows | Bytes | Cost (%CPU) | Time      |
-----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT          |             |   10 |   540 |    1076 (9) |  00:00:01 |
|* 1 | TABLE ACCESS STORAGE FULL | DAVID_BOWIE |   10 |   540 |    1076 (9) |  00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - storage("CODE1"=42)
     filter("CODE1"=42)

Note
-----
- automatic DOP: Computed Degree of Parallelism is 1

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      83297 consistent gets
      83285 physical reads
          0 redo size
        783 bytes sent via SQL*Net to client
        362 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         10 rows processed



SQL> select * from david_bowie where code2=42;

2000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1390211489

-----------------------------------------------------------------------------------------
| Id | Operation                 | Name        | Rows | Bytes | Cost (%CPU) | Time      |
-----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT          |             | 2068 |  109K |   1083 (10) |  00:00:01 |
|* 1 | TABLE ACCESS STORAGE FULL | DAVID_BOWIE | 2068 |  109K |   1083 (10) |  00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - storage("CODE2"=42)
     filter("CODE2"=42)

Note
-----
- automatic DOP: Computed Degree of Parallelism is 1

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      83297 consistent gets
      83285 physical reads
          0 redo size
      32433 bytes sent via SQL*Net to client
        362 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
       2000 rows processed



SQL> select * from david_bowie where code3=42;

100000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1390211489

-----------------------------------------------------------------------------------------
| Id | Operation                 | Name        | Rows | Bytes | Cost (%CPU) | Time      |
-----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT          |             | 100K | 5273K |   1090 (10) |  00:00:01 |
|* 1 | TABLE ACCESS STORAGE FULL | DAVID_BOWIE | 100K | 5273K |   1090 (10) |  00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - storage("CODE3"=42)
     filter("CODE3"=42)

Note
-----
- automatic DOP: Computed Degree of Parallelism is 1

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      83297 consistent gets
      83285 physical reads
          0 redo size
    1984026 bytes sent via SQL*Net to client
        571 bytes received via SQL*Net from client
         21 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
     100000 rows processed

Obviously with no indexes in place, they all currently use a FTS.

If we wait though until the next Automatic Indexing reporting period and look at the next Automatic Indexing report:

SQL> select dbms_auto_index.report_last_activity() from dual;

SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
Index candidates                             : 3
Indexes created (visible / invisible)        : 2 (1 / 1)
Space used (visible / invisible)             : 276.82 MB (142.61 MB / 134.22 MB)
Indexes dropped                              : 0
SQL statements verified                      : 2
SQL statements improved (improvement factor) : 1 (83301.1x)
SQL plan baselines created                   : 0
Overall improvement factor                   : 2x
-------------------------------------------------------------------------------

SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
Unused indexes   : 0
Space used       : 0 B
Unusable indexes : 0
-------------------------------------------------------------------------------

We notice Automatic Indexing stated there were 3 index candidates, but has created 2 new indexes, one VISIBLE and one INVISIBLE.

Further down the report:

INDEX DETAILS
-------------------------------------------------------------------------------
The following indexes were created:
-------------------------------------------------------------------------------
----------------------------------------------------------------------------
| Owner | Table       | Index                | Key   | Type   | Properties |
----------------------------------------------------------------------------
| BOWIE | DAVID_BOWIE | SYS_AI_48d67aycauayj | CODE1 | B-TREE | NONE       |
| BOWIE | DAVID_BOWIE | SYS_AI_cpw2p477wk6us | CODE2 | B-TREE | NONE       |
----------------------------------------------------------------------------
-------------------------------------------------------------------------------

We see that one index was created on the CODE1 column and the other on the CODE2 column (note: in the current 19.12.0.1.0 version of the Transaction Processing Autonomous Database, the * to denote invisible indexes above is no longer present).

No index is listed as being created on the very unselective CODE3 column.

If we continue down the report:

VERIFICATION DETAILS
-------------------------------------------------------------------------------
The performance of the following statements improved:
-------------------------------------------------------------------------------
Parsing Schema Name : BOWIE
SQL ID              : 6vp85adas9tq3
SQL Text            : select * from david_bowie where code1=42
Improvement Factor  : 83301.1x

Execution Statistics:
-----------------------------
                     Original Plan                Auto Index Plan
                     ---------------------------- ----------------------------
Elapsed Time (s):    246874                       1248
CPU Time (s):        139026                       694
Buffer Gets:         749710                       13
Optimizer Cost:      1076                         13
Disk Reads:          749568                       2
Direct Writes:       0                            0
Rows Processed:      90                           10
Executions:          9                            1

PLANS SECTION
--------------------------------------------------------------------------------
-------------

- Original
-----------------------------
Plan Hash Value : 1390211489

-----------------------------------------------------------------------------------
| Id | Operation                 | Name        | Rows | Bytes | Cost | Time       |
-----------------------------------------------------------------------------------
|  0 | SELECT STATEMENT          |             |      |       | 1076 |            |
|  1 | TABLE ACCESS STORAGE FULL | DAVID_BOWIE |   10 |   540 | 1076 |   00:00:01 |
-----------------------------------------------------------------------------------

Notes
-----
- dop = 1
- px_in_memory_imc = no
- px_in_memory = no

- With Auto Indexes
-----------------------------
Plan Hash Value : 3510800558

-------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows | Bytes | Cost | Time       |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |   10 |   540 |   13 |   00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID BATCHED | DAVID_BOWIE          |   10 |   540 |   13 |   00:00:01 |
| * 2 | INDEX RANGE SCAN                    | SYS_AI_48d67aycauayj |   10 |       |    3 |   00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("CODE1"=42)

Notes
-----
- Dynamic sampling used for this statement ( level = 11 )

We see that the Visible Index was actually created on the CODE1 column, thanks to the perceived 83301.1x performance improvement.

If we look at the status of all indexes now on our table:

SQL> select i.index_name, c.column_name, i.auto, i.constraint_index, i.visibility, i.compression, i.status, i.num_rows, i.leaf_blocks, i.clustering_factor
from user_indexes i, user_ind_columns c where i.index_name=c.index_name and i.table_name='DAVID_BOWIE';

INDEX_NAME             COLUMN_NAME AUT CON VISIBILIT COMPRESSION   STATUS     NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
---------------------- ----------- --- --- --------- ------------- -------- ---------- ----------- -----------------
SYS_AI_48d67aycauayj   CODE1       YES NO  VISIBLE   ADVANCED LOW  VALID      10000000       16891          10000000
SYS_AI_cpw2p477wk6us   CODE2       YES NO  INVISIBLE ADVANCED LOW  VALID      10000000       15369          10000000
SYS_AI_c8bkc2z4bxrzp   CODE3       YES NO  INVISIBLE ADVANCED LOW  UNUSABLE   10000000       20346           4173285

We see indexes with 3 different statuses:

• CODE1 index is VISIBLE/VALID
• CODE2 index is INVISIBLE/VALID
• CODE3 index is INVISIBLE/UNUSABLE

The logic appears to be as follows:

If an index will demonstrably improve performance sufficiently, then the index is created as a VISIBLE and VALID index and can be subsequently used by the CBO.

If an index is demonstrably awful and has very little chance of ever being used by the CBO, it’s left INVISIBLE and put in an UNUSABLE state. It therefore takes up no space and will eventually be dropped. It will likely never be required, so no loss then if it doesn’t physically exist.

Interestingly, if an index is somewhat “borderline”, currently not efficient enough to be used by the CBO, but close enough perhaps that maybe things might change in the future to warrant such as index, then it is physically created as VALID but is not readily available to the CBO and remains in an INVISIBLE state. This index won’t have to be rebuilt in the future if indeed things change subsequently to enough to warrant future index usage.

It should of be noted that little of this is clearly documented and that it’s subject to change without notice. One of the key points of Automatic Indexing is that we can off-hand all this to Oracle and let Oracle worry about things. That said, it might be useful to understand why you might end up with indexes in different statuses and the subsequent impact this might make.

If we re-run the first query based on the CODE1 predicate:

SQL> select * from david_bowie where code1=42;

10 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3510800558

------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows | Bytes | Cost (%CPU) | Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |   10 |   540 |      14 (0) | 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID BATCHED | DAVID_BOWIE          |   10 |   540 |      14 (0) | 00:00:01 |
| * 2 | INDEX RANGE SCAN                    | SYS_AI_48d67aycauayj |   10 |       |       3 (0) | 00:00:01 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("CODE1"=42)

Note
-----
- automatic DOP: Computed Degree of Parallelism is 1

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
         14 consistent gets
          0 physical reads
          0 redo size
       1151 bytes sent via SQL*Net to client
        362 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         10 rows processed

The CBO will indeed use the newly created Automatic Index.

But if we re-run either of the other 2 queries based on the CODE2 and CODE3 predicates:

SQL> select * from david_bowie where code2=42;

2000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1390211489

-----------------------------------------------------------------------------------------
| Id  | Operation                 | Name        | Rows | Bytes | Cost (%CPU) | Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |             | 2068 |  109K |   1083 (10) | 00:00:01 |
| * 1 | TABLE ACCESS STORAGE FULL | DAVID_BOWIE | 2068 |  109K |   1083 (10) | 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - storage("CODE2"=42)
    filter("CODE2"=42)

Note
-----
- automatic DOP: Computed Degree of Parallelism is 1

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      83297 consistent gets
      83285 physical reads
          0 redo size
      32433 bytes sent via SQL*Net to client
        362 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
       2000 rows processed

The CBO will not use an index as no VISIBLE/VALID indexes exist on these columns.

In future blog posts I’ll explore what is meant by “borderline” and what can subsequently happen to any such INVISIBLE/VALID Automatic Indexes…