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.