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. 
 
 

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

In the previous post we discussed how automatic indexing (AI) does not currently support creating an index based on a function or expression predicate even if it’s an equality predicate. You must manually create the associated function-based index.
 
However if we got access to the application, there’s a better strategy when frequently searching on a function-based predicate. That is to create an extended stats on the function-based predicate.  
 
Since Oracle 11g, Oracle has supported the use of extended stats – kind of hidden virtual column that defines the statistics on a function/expression that can be easily accessed and used by CBO when queried.
 
We will next create an extended stats on the expression UPPER(CUSTOMER_NAME)
 
ai_demo@PDB19> select extension_name, extension
  2  from user_stat_extensions
  3  where table_name ='DEMO028';
 
no rows selected
 
ai_demo@PDB19> begin
  2      dbms_stats.gather_table_stats(user,'DEMO028',
  3          method_opt=>'for columns (upper(customer_name)) size 2048',
  4          options=>'gather auto',
  5          no_invalidate=>false) ;
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
ai_demo@PDB19> select extension_name, extension
  2  from user_stat_extensions
  3  where table_name ='DEMO028';
 
EXTENSION_NAME                      EXTENSION
----------------------------------- ------------------------------
SYS_STU4G2A1QWESGJY9ZOLLFV4O1H      (UPPER("CUSTOMER_NAME"))
 
ai_demo@PDB19> select num_distinct, num_nulls, num_buckets,histogram
  2  from user_tab_cols
  3  where table_name ='DEMO028'
  4  and column_name like 'SYS%';
 
NUM_DISTINCT  NUM_NULLS NUM_BUCKETS HISTOGRAM
------------ ---------- ----------- ---------------
     9893888          0        2048 HYBRID
 
Note how the expression UPPER(CUSTOMER_NAME) got associated with a hidden virtual column and now has up to date statistics.
 
We can now run this simple query based on the existing expression on the predicate.
 
ai_demo@PDB19> select * from demo028 where upper(customer_name) ='DEMO_USER24';
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3440896456
 
-------------------------------------------------------------------------------------
| Id  | Operation                 | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |         |     1 |   129 | 47911   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS STORAGE FULL| DEMO028 |     1 |   129 | 47911   (1)| 00:00:02 |
-------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - storage(UPPER("CUSTOMER_NAME")='DEMO_USER24')
       filter(UPPER("CUSTOMER_NAME")='DEMO_USER24')
 
 
Statistics
----------------------------------------------------------
          0  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
 
 
If we look at the portions of the subsequent AI report  
 
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
 Index candidates                              : 1
 Indexes created (visible / invisible)         : 1 (1 / 0)
 Space used (visible / invisible)              : 327.16 MB (327.16 MB / 0 B)
 Indexes dropped                               : 0
 SQL statements verified                       : 1
 SQL statements improved (improvement factor)  : 1 (175157x)
 SQL plan baselines created                    : 0
 Overall improvement factor                    : 175157x
-------------------------------------------------------------------------------
 
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 | DEMO028 | SYS_AI_bnapvf6v3ngpt | SYS_STU4G2A1QWESGJY9ZOLLFV4O1H | B-TREE | NONE       |
---------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
 
VERIFICATION DETAILS
-------------------------------------------------------------------------------
1. The performance of the following statements improved:
-------------------------------------------------------------------------------
 Parsing Schema Name  : AI_DEMO
 SQL ID               : d5fqywk4bch6s
 SQL Text             : select * from demo028 where upper(customer_name)
                      ='DEMO_USER24'
 Improvement Factor   : 175157x
 
Execution Statistics:
-----------------------------
                    Original Plan                 Auto Index Plan
                    ----------------------------  ----------------------------
 Elapsed Time (s):  8150989                       1206
 CPU Time (s):      8050545                       963
 Buffer Gets:       525471                        4
 Optimizer Cost:    47911                         4
 Disk Reads:        0                             2
 Direct Writes:     0                             0
 Rows Processed:    3                             1
 Executions:        3                             1
 
 
PLANS SECTION
---------------------------------------------------------------------------------------------
 
- Original
-----------------------------
 Plan Hash Value  : 3440896456
 
--------------------------------------------------------------------------------
| Id | Operation                   | Name    | Rows | Bytes | Cost  | Time     |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |         |      |       | 47911 |          |
|  1 |   TABLE ACCESS STORAGE FULL | DEMO028 |    1 |   129 | 47911 | 00:00:02 |
--------------------------------------------------------------------------------
 
- With Auto Indexes
-----------------------------
 Plan Hash Value  : 1011955271
 
-------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                 | Rows | Bytes | Cost | Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                      |    1 |   129 |    4 | 00:00:01 |
|   1 |   TABLE ACCESS BY INDEX ROWID BATCHED | DEMO028              |    1 |   129 |    4 | 00:00:01 |
| * 2 |    INDEX RANGE SCAN                   | SYS_AI_bnapvf6v3ngpt |    1 |       |    3 | 00:00:01 |
-------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access(UPPER("CUSTOMER_NAME")='DEMO_USER24')
 
 
Notes
-----
- Dynamic sampling used for this statement ( level = 11 )
 
 
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
 
 
We see from the report that AI has now created the associated function-based index (SYS_AI_bnapvf6v3ngpt) based on the expression.
 
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';
 
TABLE_NAME INDEX_NAME                INDEXIN AUT STATUS   VISIBILIT CLUSTERING_FACTOR LEAF_BLOCKS
---------- ------------------------- ------- --- -------- --------- ----------------- -----------
DEMO028    SYS_AI_bnapvf6v3ngpt      FULL    YES VALID    VISIBLE             2042101       38911
 
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;
 
TABLE_NAME INDEX_NAME                COLUMN_NAME                         COLUMN_POSITION
---------- ------------------------- ----------------------------------- ---------------
DEMO028    SYS_AI_bnapvf6v3ngpt      SYS_STU4G2A1QWESGJY9ZOLLFV4O1H                    1
 
If we rerun the existing sql query now.
 
ai_demo@PDB19> select * from demo028 where upper(customer_name) ='DEMO_USER24';
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1011955271
 
------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |     1 |   129 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DEMO028              |     1 |   129 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | SYS_AI_bnapvf6v3ngpt |     1 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access(UPPER("CUSTOMER_NAME")='DEMO_USER24')
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  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
 
The CBO now uses the new AI to significantly improve the performance of the query, so not only is using a user defined extended stats a cleaner solution with respect to the frequent use of function-based expressions but has the added advantage of being supported with AI.