Automatic Indexing - Part XXIII (AI limitations..)

Have been waiting for while to post a series of various limitations associated with automatic indexing (AI), one of the most serious limitation is associate with range-based predicates, going to run a series of range-based predicates that heavily filter and would greatly benefit from an index.
 
ai_demo@PDB19> create table demo025
  2  nologging as
  3  select a.*,
  4         sysdate - mod(rownum,2800) as release_dt ,
  5         ceil( dbms_random.value(1,500000) ) as sales_amt,
  6         rownum as sales_id
  7  from stage a, stage b, stage c
  8  where rownum <= 10000000 ;
 
Table created.
 
Then I run the following range scan queries several times that each return exactly only few rows.
 
ai_demo@PDB19> set autotrace traceonly exp statistics
ai_demo@PDB19> select * from demo025 where sales_id between 42 and 50;
 
9 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 625612404
 
-------------------------------------------------------------------------------------
| Id  | Operation                 | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |         |    10 |  1140 | 48595   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS STORAGE FULL| DEMO025 |    10 |  1140 | 48595   (1)| 00:00:02 |
-------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - storage("SALES_ID"<=50 AND "SALES_ID">=42)
       filter("SALES_ID"<=50 AND "SALES_ID">=42)
 
ai_demo@PDB19> select * from demo025 where sales_id < 0;
 
no rows selected
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 625612404
 
-------------------------------------------------------------------------------------
| Id  | Operation                 | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |         |     1 |   114 | 48595   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS STORAGE FULL| DEMO025 |     1 |   114 | 48595   (1)| 00:00:02 |
-------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - storage("SALES_ID"<0)
       filter("SALES_ID"<0)
 
ai_demo@PDB19> select * from demo025 where sales_id > 10000000 ;
 
no rows selected
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 625612404
 
-------------------------------------------------------------------------------------
| Id  | Operation                 | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |         |     1 |   114 | 48595   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS STORAGE FULL| DEMO025 |     1 |   114 | 48595   (1)| 00:00:02 |
-------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - storage("SALES_ID">10000000)
       filter("SALES_ID">10000000)
 
ai_demo@PDB19> set autotrace off
 
 
if we look at the subsequent AI report, 
 
 
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
 Index candidates                              : 2
 Indexes created (visible / invisible)         : 1 (1 / 0)
 Space used (visible / invisible)              : 137.36 MB (137.36 MB / 0 B)
 Indexes dropped                               : 0
 SQL statements verified                       : 1
 SQL statements improved (improvement factor)  : 1 (10504.7x)
 SQL plan baselines created                    : 0
 Overall improvement factor                    : 10504.7x
-------------------------------------------------------------------------------
 
SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
 Unused indexes    : 0
 Space used        : 0 B
 Unusable indexes  : 0
-------------------------------------------------------------------------------
 
INDEX DETAILS
-------------------------------------------------------------------------------
1. The following indexes were created:
*: invisible
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
| Owner   | Table   | Index                | Key        | Type   | Properties |
-------------------------------------------------------------------------------
| AI_DEMO | DEMO023 | SYS_AI_11muvn2pw0ym6 | RELEASE_DT | B-TREE | LOCAL      |
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
 
We notice NO index were created on DEMO025 table.
 
Currently AI do not support non-equality predicates, AI are only created based on the equality based predicates. Obviously, Automatic Indexing is a fabulous feature and this might all change in the future. But with Non-Equality predicates so prevalent in SQL, it’s vital to note this current limitation when using and relying on Automatic Indexing…