Automatic indexing - Part XXVII (non equality based predicates on 21c)

In the pervious post, we discussed how automatic indexing (AI) supported the creation of indexes based on equality-based predicates. One of the most significant improvements with AI introduced in Oracle database 21c is that non-equality-based predicates are now supported.
 
The following testcase was executed from ATP instance running on Oracle 21c (21.3) we see this
 
ai_demo@ATPDEMO> create table demo030
  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 ;
 
Then we run few queries supporting non-equality based predicates.
 
ai_demo@ATPDEMO> begin
  2  for i in 1..5
  3  loop
  4    for x in (select * from demo030 where sales_id between 42 and 50)
  5    loop null; end loop;
  6  end loop;
  7  end ;
  8* /
 
PL/SQL procedure successfully completed.
 
ai_demo@ATPDEMO> begin
  2  for i in 1..5
  3  loop
  4   for x in (select * from demo030 where sales_id < 0)
  5    loop null; end loop ;
  6  end loop;
  7  end ;
  8* /
 
PL/SQL procedure successfully completed.
 
ai_demo@ATPDEMO> begin
  2  for i in 1..5
  3  loop
  4     for x in (select * from demo030 where sales_id > 10000000)
  5     loop null; end loop ;
  6  end loop;
  7  end ;
  8* /
 
PL/SQL procedure successfully completed.
 
And wait for the AI process to kick in to see what it come up.
 
ai_demo@ATPDEMO> set echo on linesize 1000 trimspool on trim on long 2000000 longchunksize 2000000
ai_demo@ATPDEMO> select dbms_auto_index.report_activity(systimestamp -1/24, systimestamp,'TEXT','ALL','ALL') report from dual;
 
REPORT   

-------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
 Activity start               : 27-JUL-2022 16:11:46
 Activity end                 : 27-JUL-2022 17:11:46
 Executions completed         : 4
 Executions interrupted       : 0
 Executions with fatal error  : 0
-------------------------------------------------------------------------------
 
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
 Index candidates                              : 1
 Indexes created (visible / invisible)         : 1 (1 / 0)
 Space used (visible / invisible)              : 201.33 MB (201.33 MB / 0 B)
 Indexes dropped                               : 0
 SQL statements verified                       : 2
 SQL statements improved (improvement factor)  : 2 (184942.1x)
 SQL plan baselines created                    : 0
 Overall improvement factor                    : 184942.1x
-------------------------------------------------------------------------------
 
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 | DEMO030 | SYS_AI_0uxjpwjw7xgh1 | SALES_ID | B-TREE | NONE       |
-----------------------------------------------------------------------------
-------------------------------------------------------------------------------
 
VERIFICATION DETAILS
-------------------------------------------------------------------------------
1. The performance of the following statements improved:
-------------------------------------------------------------------------------
 Parsing Schema Name  : AI_DEMO
 SQL ID               : 6hkq4bs4c297b
 SQL Text             : SELECT * FROM DEMO030 WHERE SALES_ID BETWEEN 42 AND 50
 Improvement Factor   : 185029.2x
 
Execution Statistics:
-----------------------------
                    Original Plan                 Auto Index Plan
                    ----------------------------  ----------------------------
 Elapsed Time (s):  308898                        1176
 CPU Time (s):      120760                        865
 Buffer Gets:       925147                        4
 Optimizer Cost:    50517                         4
 Disk Reads:        924247                        3
 Direct Writes:     0                             0
 Rows Processed:    45                            9
 Executions:        5                             1
 
 
PLANS SECTION
---------------------------------------------------------------------------------------------
 
- Original
-----------------------------
 Plan Hash Value  : 3833916346
 
--------------------------------------------------------------------------------
| Id | Operation                   | Name    | Rows | Bytes | Cost  | Time     |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |         |      |       | 50517 |          |
|  1 |   TABLE ACCESS STORAGE FULL | DEMO030 |    8 |   928 | 50517 | 00:00:02 |
--------------------------------------------------------------------------------
 
Notes
-----
- dop = 1
- px_in_memory_imc = no
- px_in_memory = no
 
 
- With Auto Indexes
-----------------------------
 Plan Hash Value  : 206159950
 
-------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                 | Rows | Bytes | Cost | Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                      |    8 |   928 |    4 | 00:00:01 |
|   1 |   TABLE ACCESS BY INDEX ROWID BATCHED | DEMO030              |    8 |   928 |    4 | 00:00:01 |
| * 2 |    INDEX RANGE SCAN                   | SYS_AI_0uxjpwjw7xgh1 |    9 |       |    3 | 00:00:01 |
-------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("SALES_ID">=42 AND "SALES_ID"<=50)
 
 
Notes
-----
- Dynamic sampling used for this statement ( level = 11 )
 
 
-------------------------------------------------------------------------------
 Parsing Schema Name  : AI_DEMO
 SQL ID               : 95ptmsmk29pvu
 SQL Text             : SELECT * FROM DEMO030 WHERE SALES_ID < 0
 Improvement Factor   : 184855x
 
Execution Statistics:
-----------------------------
                    Original Plan                 Auto Index Plan
                    ----------------------------  ----------------------------
 Elapsed Time (s):  213942                        75
 CPU Time (s):      73496                         75
 Buffer Gets:       924275                        3
 Optimizer Cost:    50517                         4
 Disk Reads:        924245                        0
 Direct Writes:     0                             0
 Rows Processed:    0                             0
 Executions:        5                             1
 
 
PLANS SECTION
---------------------------------------------------------------------------------------------
 
- Original
-----------------------------
 Plan Hash Value  : 3833916346
 
--------------------------------------------------------------------------------
| Id | Operation                   | Name    | Rows | Bytes | Cost  | Time     |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |         |      |       | 50517 |          |
|  1 |   TABLE ACCESS STORAGE FULL | DEMO030 |    1 |   116 | 50517 | 00:00:02 |
--------------------------------------------------------------------------------
 
Notes
-----
- dop = 1
- px_in_memory_imc = no
- px_in_memory = no
 
 
- With Auto Indexes
-----------------------------
 Plan Hash Value  : 206159950
 
-------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                 | Rows | Bytes | Cost | Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                      |    1 |   116 |    4 | 00:00:01 |
|   1 |   TABLE ACCESS BY INDEX ROWID BATCHED | DEMO030              |    1 |   116 |    4 | 00:00:01 |
| * 2 |    INDEX RANGE SCAN                   | SYS_AI_0uxjpwjw7xgh1 |    1 |       |    3 | 00:00:01 |
-------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("SALES_ID"<0)
 
 
Notes
-----
- Dynamic sampling used for this statement ( level = 11 )
 
 
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
 
ai_demo@ATPDEMO> select table_name,index_name,indexing,auto,
  2          status,visibility,clustering_factor,leaf_blocks
  3  from all_indexes
  4  where table_name = 'DEMO030'
  5* and owner ='AI_DEMO';
 
TABLE_NAME    INDEX_NAME              INDEXING    AUTO    STATUS    VISIBILITY       CLUSTERING_FACTOR    LEAF_BLOCKS
_____________ _______________________ ___________ _______ _________ _____________ ____________________ ______________
DEMO030       SYS_AI_0uxjpwjw7xgh1    FULL        YES     VALID     VISIBLE                     184849          23558
 
ai_demo@ATPDEMO> select table_name,index_name,column_name,column_position
  2  from user_ind_columns
  3  where table_name = 'DEMO030'
  4* order by 1,2;
 
TABLE_NAME    INDEX_NAME              COLUMN_NAME       COLUMN_POSITION
_____________ _______________________ ______________ __________________
DEMO030       SYS_AI_0uxjpwjw7xgh1    SALES_ID                        1
 
If we look at the plans for all these equivalent query, post the AI in place
 
ai_demo@ATPDEMO> explain plan for select * from demo030 where sales_id between 42 and 50;
 
Explained.
 
ai_demo@ATPDEMO> select * from table( dbms_xplan.display );
 
PLAN_TABLE_OUTPUT
________________________________________________________________________________________________
Plan hash value: 206159950
 
------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows  | Bytes | Cost (%CPU)
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |     8 |   928 |     4   (0)
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DEMO030              |     8 |   928 |     4   (0)
|*  2 |   INDEX RANGE SCAN                  | SYS_AI_0uxjpwjw7xgh1 |     8 |       |     3   (0)
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("SALES_ID">=42 AND "SALES_ID"<=50)
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1
 
ai_demo@ATPDEMO> explain  plan for select * from demo030 where sales_id < 0;
 
Explained.
 
ai_demo@ATPDEMO> select * from table( dbms_xplan.display );
 
PLAN_TABLE_OUTPUT
_________________________________________________________________________________________________
Plan hash value: 206159950
 
-------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |     1 |   116 |     4   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DEMO030              |     1 |   116 |     4   (0)|
|*  2 |   INDEX RANGE SCAN                  | SYS_AI_0uxjpwjw7xgh1 |     1 |       |     3   (0)|
-------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("SALES_ID"<0)
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1
 
ai_demo@ATPDEMO> explain  plan for select * from demo030 where sales_id > 10000000 ;
 
Explained.
 
ai_demo@ATPDEMO> select * from table( dbms_xplan.display );
 
PLAN_TABLE_OUTPUT
_________________________________________________________________________________________________
Plan hash value: 206159950
 
-------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |     1 |   116 |     4   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DEMO030              |     1 |   116 |     4   (0)|
|*  2 |   INDEX RANGE SCAN                  | SYS_AI_0uxjpwjw7xgh1 |     1 |       |     3   (0)|
-------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("SALES_ID">10000000)
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1
 
18 rows selected.
 
ai_demo@ATPDEMO> select banner_full from v$version;
 
BANNER_FULL
_______________________________________________________________________________
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
 
So automatic indexing has kicked in and significantly improved the performance of all these queries. AI still have number of restrictions, but by supporting non-equality predicates, it has now removed its most significant restriction.