Automatic indexing - Not supported - Part I

As discussed previously latest version of Oracle (21c (21.3)) Autonomous database running on Oracle cloud has increased the number of scenarios in which it will now create automatic indexes (AI), such as with non-equality predicates and JSON expressions.
 
However even with the latest version of Oracle database, there are still several scenarios where an automatic index will not be created, even though an index might prove beneficial.
 
One such scenario is when the min/max of a column is required.
 
Oracle can effectively use an index to determine either the min or max value of a column by just visiting the first or the last leaf block of an index. The index full scan (min/max) access path can be used explicitly for this purpose.
 
We will create a simple table like this
 
ai_demo@ATP21C> create table demo033
  2  nologging as
  3  select rownum as id, a.*
  4  from stage a, stage b
  5  where rownum <= 10000000 ;
 
Table created.
 
 Then we will run the following queries few time that return the min value of the ID column.
 
ai_demo@ATP21C> set autotrace traceonly exp statistics
ai_demo@ATP21C> select min(id) from demo033 ;
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 4169885461
 
--------------------------------------------------------------------------------------
| Id  | Operation                  | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |         |     1 |     6 | 45385   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE            |         |     1 |     6 |            |          |
|   2 |   TABLE ACCESS STORAGE FULL| DEMO033 |    10M|    57M| 45385   (1)| 00:00:02 |
--------------------------------------------------------------------------------------
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     166628  consistent gets
     166622  physical reads
          0  redo size
        376  bytes sent via SQL*Net to client
         48  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
 
 
Currently the CBO has no choice but to use a full table scan as there is currently no index on the ID column. So what does AI make of things?
 
Nothing, currently AI will not create index in this scenario, no matter how many times we execute these queries. If we look at the index on the table after a significant period of time running these queries.
 
ai_demo@ATP21C> select dbms_auto_index.report_activity(systimestamp -1/24, systimestamp,'TEXT','ALL','ALL') report from dual;
 
REPORT
-------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
 Activity start               : 11-AUG-2022 09:31:11
 Activity end                 : 11-AUG-2022 10:31:11
 Executions completed         : 4
 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
-------------------------------------------------------------------------------
 
ai_demo@ATP21C> select table_name,index_name,index_type,indexing,auto,
  2          status,visibility
  3  from all_indexes
  4  where table_name = 'DEMO033'
  5  and owner ='AI_DEMO';
 
no rows selected
 
ai_demo@ATP21C> select table_name,index_name,column_name,column_position
  2  from user_ind_columns
  3  where table_name = 'DEMO033'
  4  order by 1,2;
 
no rows selected
 
no automatic index, to improve the performance of these queries, we currently have to manually create the associated index.
 
ai_demo@ATP21C> create index demo033_idx1 on demo033(id);
 
Index created.
 
If we now re-run these queries and look at the execution plan
 
ai_demo@ATP21C> set autotrace traceonly exp statistics
ai_demo@ATP21C> select min(id) from demo033 ;
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3449641509
 
-------------------------------------------------------------------------------------------
| Id  | Operation                  | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |              |     1 |     6 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |              |     1 |     6 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| DEMO033_IDX1 |     1 |     6 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        376  bytes sent via SQL*Net to client
         48  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 

We can see that the CBO is now indeed using the index to return the min/max value with a vastly reduced number of consistent gets (down to 3 from the previous 166K).