Automatic Indexing - Part XXV (function based indexes..)

The topic for discussion today is how Automatic indexing (AI) support function-based index? Let’s takes a look, the following table has a key column CUSTOMER_NAME which is effectively unique varchar2 column.
 
ai_demo@PDB19> create table demo028
  2  nologging as
  3  select a.*,
  4         'DEMO_USER' || rownum as customer_name
  5  from stage a, stage b, stage c
  6  where rownum <= 10000000 ;
 
Table created.
 
ai_demo@PDB19>
 
Let’s run the following query with UPPER function-based predicates that returns only the one row
 
ai_demo@PDB19> select * from demo028 where upper(customer_name) ='DEMO_USER55';
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3440896456
 
-------------------------------------------------------------------------------------
| Id  | Operation                 | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |         |   100K|    10M| 47911   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS STORAGE FULL| DEMO028 |   100K|    10M| 47911   (1)| 00:00:02 |
-------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - storage(UPPER("CUSTOMER_NAME")='DEMO_USER55')
       filter(UPPER("CUSTOMER_NAME")='DEMO_USER55')
 
 
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     175157  consistent gets
          0  physical reads
          0  redo size
       2083  bytes sent via SQL*Net to client
        546  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
What does AI make of this scenario? basically it does nothing. Currently AI does not support such function based index, even with equality based predicates (as of at the version 19.14). if we look at the next AI report
 
 
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  : 0x
-------------------------------------------------------------------------------
 
SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
 Unused indexes    : 0
 Space used        : 0 B
 Unusable indexes  : 0
-------------------------------------------------------------------------------
 
No such function-based index is ever created by AI:
 
ai_demo@PDB19> select table_name,index_name,indexing,auto,
  2          status,visibility,clustering_factor,leaf_blocks
  3  from all_indexes
  4  where table_name ='DEMO028'
  5  and owner ='AI_DEMO';
 
no rows selected
 
ai_demo@PDB19> select table_name,index_name,column_name,column_position
  2  from user_ind_columns
  3  where table_name ='DEMO028'
  4  order by 1,2;
 
no rows selected