Automatic Indexing - Part VII

In the pervious blog post on Automatic Indexing (AI) we discussed how the default index column order is column id, the order in which the columns are defined in the table, but what if there are other factors based on new workloads and the original index column order is no longer optimal or appropriate?
 
Will begin by creating a table
 
ai_demo@PDB19> create table DEMO007e
  2  nologging as
  3  select rownum as id,
  4         mod(rownum,10)+1 as x1,
  5         mod(rownum,100)+1     as x2,
  6         mod(rownum,500000)+1 as x3,
  7         a.*
  8  from all_objects a,
  9         all_users, all_users
 10  where rownum <=10000000;
 
Table created.
 
Having key columns X1, X2 and X3 if we now run the following query with predicates based on three columns
 
ai_demo@PDB19> select count(*) from demo007e
  2  where x1 = 2 and x2 = 52
  3  and x3 = 52 ;
 
  COUNT(*)
----------
        20
 
After the default 15 min period in which the AI task is run, if we look at what AI has been created.
 
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
 Index candidates                              : 1
 Indexes created (visible / invisible)         : 1 (1 / 0)
 Space used (visible / invisible)              : 142.61 MB (142.61 MB / 0 B)
 Indexes dropped                               : 0
 SQL statements verified                       : 1
 SQL statements improved (improvement factor)  : 1 (198371.5x)
 SQL plan baselines created                    : 0
 Overall improvement factor                    : 198371.5x
-------------------------------------------------------------------------------
 
INDEX DETAILS
-------------------------------------------------------------------------------
1. The following indexes were created:
-------------------------------------------------------------------------------
------------------------------------------------------------------------------
| Owner   | Table    | Index                | Key      | Type   | Properties |
------------------------------------------------------------------------------
| AI_DEMO | DEMO007E | SYS_AI_0jbgd6ymtua6u | X1,X2,X3 | B-TREE | NONE       |
------------------------------------------------------------------------------
-------------------------------------------------------------------------------
VERIFICATION DETAILS
-------------------------------------------------------------------------------
1. The performance of the following statements improved:
-------------------------------------------------------------------------------
 Parsing Schema Name  : AI_DEMO
 SQL ID               : 9puwds50csb4s
 SQL Text             : select count(*) from demo007e where x1 = 2 and x2 = 52
                      and x3 = 52
 Improvement Factor   : 198371.5x
 
Execution Statistics:
-----------------------------
                    Original Plan                 Auto Index Plan
                    ----------------------------  ----------------------------
 Elapsed Time (s):  1092449                       1386
 CPU Time (s):      164804                        835
 Buffer Gets:       396753                        3
 Optimizer Cost:    54112                         3
 Disk Reads:        396704                        2
 Direct Writes:     0                             0
 Rows Processed:    2                             1
 Executions:        2                             1
 
 
PLANS SECTION
---------------------------------------------------------------------------------------------
 
- Original
-----------------------------
 Plan Hash Value  : 1147698856
 
----------------------------------------------------------------------------------
| Id | Operation                    | Name     | Rows | Bytes | Cost  | Time     |
----------------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |          |      |       | 54112 |          |
|  1 |   SORT AGGREGATE             |          |    1 |    11 |       |          |
|  2 |    TABLE ACCESS STORAGE FULL | DEMO007E |    1 |    11 | 54112 | 00:00:03 |
----------------------------------------------------------------------------------
 
- With Auto Indexes
-----------------------------
 Plan Hash Value  : 809355571
 
-------------------------------------------------------------------------------------
| Id  | Operation           | Name                 | Rows | Bytes | Cost | Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                      |    1 |    11 |    3 | 00:00:01 |
|   1 |   SORT AGGREGATE    |                      |    1 |    11 |      |          |
| * 2 |    INDEX RANGE SCAN | SYS_AI_0jbgd6ymtua6u |   63 |   693 |    3 | 00:00:01 |
-------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("X1"=2 AND "X2"=52 AND "X3"=52)
 
 
Notes
-----
- Dynamic sampling used for this statement ( level = 11 )
 
 
We can see Oracle has indeed created an AI (SYS_AI_0jbgd6ymtua6u) in the default X1, X2 and X3 column order.
 
ai_demo@PDB19> select owner,index_name,tablespace_name,compression,
  2         status,visibility,indexing,auto
  3  from all_indexes
  4  where table_name ='DEMO007E'
  5  and owner ='AI_DEMO';
 
OWNER   INDEX_NAME           TABLESPACE_NAME COMPRESSION   STATUS   VISIBILIT INDEXIN AUT
------- -------------------- --------------- ------------- -------- --------- ------- ---
AI_DEMO SYS_AI_0jbgd6ymtua6u TS_INDEX_DEMO   ADVANCED LOW  VALID    VISIBLE   FULL    YES
 
ai_demo@PDB19> select index_name,column_name,column_position
  2  from user_ind_columns
  3  where table_name ='DEMO007E'
  4  order by 1,3;
 
INDEX_NAME                COLUMN_NAM COLUMN_POSITION
------------------------- ---------- ---------------
SYS_AI_0jbgd6ymtua6u      X1                       1
SYS_AI_0jbgd6ymtua6u      X2                       2
SYS_AI_0jbgd6ymtua6u      X3                       3
 
 
But if we now run a new query, based on a predicate on just the X3 column
 
ai_demo@PDB19> set autotrace traceonly exp statistics
ai_demo@PDB19> select max(object_id) from demo007e
  2  where x3 = 52 ;
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1147698856
 
---------------------------------------------------------------------------------------
| Id  | Operation                  | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |          |     1 |     9 | 54112   (1)| 00:00:03 |
|   1 |  SORT AGGREGATE            |          |     1 |     9 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| DEMO007E |    20 |   180 | 54112   (1)| 00:00:03 |
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - storage("X3"=52)
       filter("X3"=52)
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     198358  consistent gets
     198352  physical reads
          0  redo size
        366  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
 
We can see the CBO has not used the index, as the leading column of the existing index is not mentioned in the SQL Predicates and the CBO deems an index skip scan as too expensive compared to Full scans.
 
If we now run an SQL Predicates based on just the columns X2 and X3
 
ai_demo@PDB19> set autotrace traceonly exp statistics
ai_demo@PDB19> select max(object_id) from demo007e
  2  where x3 = 52
  3  and x2 = 52;
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1147698856
 
---------------------------------------------------------------------------------------
| Id  | Operation                  | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |          |     1 |    12 | 54112   (1)| 00:00:03 |
|   1 |  SORT AGGREGATE            |          |     1 |    12 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| DEMO007E |    20 |   240 | 54112   (1)| 00:00:03 |
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - storage("X3"=52 AND "X2"=52)
       filter("X3"=52 AND "X2"=52)
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     198358  consistent gets
     198352  physical reads
          0  redo size
        366  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 existing index is again not used as important column X1 – which is the leading column in the index – is not mentioned in the SQL predicates and the CBO deems an index skip scan as expensive.
 
Given this scenario, many experienced DBA’s simply create a new index with X3, X2 as leading column. But what does AI do in this scenario.
 
INDEX DETAILS
-------------------------------------------------------------------------------
1. The following indexes were created:
-------------------------------------------------------------------------------
------------------------------------------------------------------------------
| Owner   | Table    | Index                | Key      | Type   | Properties |
------------------------------------------------------------------------------
| AI_DEMO | DEMO007E | SYS_AI_6bssjp2f127a6 | X3,X2,X1 | B-TREE | NONE       |
------------------------------------------------------------------------------
-------------------------------------------------------------------------------
 
AI report states that one index got created with the right column order.
 
VERIFICATION DETAILS
-------------------------------------------------------------------------------
1. The performance of the following statements improved:
-------------------------------------------------------------------------------
 Parsing Schema Name  : AI_DEMO
 SQL ID               : 1fvwua7gb4xth
 SQL Text             : select max(object_id) from demo007e where x3 = 52 and
                      x2 = 52
 Improvement Factor   : 19835.8x
 
Execution Statistics:
-----------------------------
                    Original Plan                 Auto Index Plan
                    ----------------------------  ----------------------------
 Elapsed Time (s):  217134                        2786
 CPU Time (s):      74347                         1468
 Buffer Gets:       595082                        23
 Optimizer Cost:    54112                         23
 Disk Reads:        595056                        20
 Direct Writes:     0                             0
 Rows Processed:    3                             1
 Executions:        3                             1
 
 
PLANS SECTION
---------------------------------------------------------------------------------------------
 
- Original
-----------------------------
 Plan Hash Value  : 1147698856
 
----------------------------------------------------------------------------------
| Id | Operation                    | Name     | Rows | Bytes | Cost  | Time     |
----------------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |          |      |       | 54112 |          |
|  1 |   SORT AGGREGATE             |          |    1 |    12 |       |          |
|  2 |    TABLE ACCESS STORAGE FULL | DEMO007E |   20 |   240 | 54112 | 00:00:03 |
----------------------------------------------------------------------------------
 
- With Auto Indexes
-----------------------------
 Plan Hash Value  : 2683870836
 
--------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                 | Rows | Bytes | Cost | Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                      |    1 |    12 |   23 | 00:00:01 |
|   1 |   SORT AGGREGATE                       |                      |    1 |    12 |      |          |
|   2 |    TABLE ACCESS BY INDEX ROWID BATCHED | DEMO007E             |   20 |   240 |   23 | 00:00:01 |
| * 3 |     INDEX RANGE SCAN                   | SYS_AI_6bssjp2f127a6 |   20 |       |    3 | 00:00:01 |
--------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("X3"=52 AND "X2"=52)
 
 
Notes
-----
- Dynamic sampling used for this statement ( level = 11 )
 
 
-------------------------------------------------------------------------------
 Parsing Schema Name  : AI_DEMO
 SQL ID               : 9puwds50csb4s
 SQL Text             : select count(*) from demo007e where x1 = 2 and x2 = 52
                      and x3 = 52
 Improvement Factor   : 198371.5x
 
Execution Statistics:
-----------------------------
                    Original Plan                 Auto Index Plan
                    ----------------------------  ----------------------------
 Elapsed Time (s):  1092449                       1386
 CPU Time (s):      164804                        835
 Buffer Gets:       396753                        3
 Optimizer Cost:    54112                         3
 Disk Reads:        396704                        2
 Direct Writes:     0                             0
 Rows Processed:    2                             1
 Executions:        2                             1
 
 
PLANS SECTION
---------------------------------------------------------------------------------------------
 
- Original
-----------------------------
 Plan Hash Value  : 1147698856
 
----------------------------------------------------------------------------------
| Id | Operation                    | Name     | Rows | Bytes | Cost  | Time     |
----------------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |          |      |       | 54112 |          |
|  1 |   SORT AGGREGATE             |          |    1 |    11 |       |          |
|  2 |    TABLE ACCESS STORAGE FULL | DEMO007E |    1 |    11 | 54112 | 00:00:03 |
----------------------------------------------------------------------------------
 
- With Auto Indexes
-----------------------------
 Plan Hash Value  : 809355571
 
-------------------------------------------------------------------------------------
| Id  | Operation           | Name                 | Rows | Bytes | Cost | Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                      |    1 |    11 |    3 | 00:00:01 |
|   1 |   SORT AGGREGATE    |                      |    1 |    11 |      |          |
| * 2 |    INDEX RANGE SCAN | SYS_AI_0jbgd6ymtua6u |   63 |   693 |    3 | 00:00:01 |
-------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("X1"=2 AND "X2"=52 AND "X3"=52)
 
 
Notes
-----
- Dynamic sampling used for this statement ( level = 11 )
 
 
-------------------------------------------------------------------------------
 Parsing Schema Name  : AI_DEMO
 SQL ID               : 9wmu0nrc8gb21
 SQL Text             : select max(object_id) from demo007e where x3 = 52
 Improvement Factor   : 16532.1x
 
Execution Statistics:
-----------------------------
                    Original Plan                 Auto Index Plan
                    ----------------------------  ----------------------------
 Elapsed Time (s):  497747                        136
 CPU Time (s):      108151                        136
 Buffer Gets:       595155                        23
 Optimizer Cost:    54112                         23
 Disk Reads:        595056                        0
 Direct Writes:     0                             0
 Rows Processed:    3                             1
 Executions:        3                             1
 
 
PLANS SECTION
---------------------------------------------------------------------------------------------
 
- Original
-----------------------------
 Plan Hash Value  : 1147698856
 
----------------------------------------------------------------------------------
| Id | Operation                    | Name     | Rows | Bytes | Cost  | Time     |
----------------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |          |      |       | 54112 |          |
|  1 |   SORT AGGREGATE             |          |    1 |     9 |       |          |
|  2 |    TABLE ACCESS STORAGE FULL | DEMO007E |   20 |   180 | 54112 | 00:00:03 |
----------------------------------------------------------------------------------
 
- With Auto Indexes
-----------------------------
 Plan Hash Value  : 2683870836
 
--------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                 | Rows | Bytes | Cost | Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                      |    1 |     9 |   23 | 00:00:01 |
|   1 |   SORT AGGREGATE                       |                      |    1 |     9 |      |          |
|   2 |    TABLE ACCESS BY INDEX ROWID BATCHED | DEMO007E             |   20 |   180 |   23 | 00:00:01 |
| * 3 |     INDEX RANGE SCAN                   | SYS_AI_6bssjp2f127a6 |   20 |       |    3 | 00:00:01 |
--------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("X3"=52)
 
 
ai_demo@PDB19> select owner,index_name,tablespace_name,compression,
  2         status,visibility,indexing,auto
  3  from all_indexes
  4  where table_name ='DEMO007E'
  5  and owner ='AI_DEMO';
 
OWNER    INDEX_NAME            TABLESPACE_NAME  COMPRESSION   STATUS   VISIBILIT INDEXIN AUT
-------- --------------------- ---------------- ------------- -------- --------- ------- ---
AI_DEMO  SYS_AI_6bssjp2f127a6  TS_INDEX_DEMO    ADVANCED LOW  VALID    VISIBLE   FULL    YES
 
ai_demo@PDB19> select index_name,column_name,column_position
  2  from user_ind_columns
  3  where table_name ='DEMO007E'
  4  order by 1,3;
 
INDEX_NAME                COLUMN_NAM COLUMN_POSITION
------------------------- ---------- ---------------
SYS_AI_6bssjp2f127a6      X3                       1
SYS_AI_6bssjp2f127a6      X2                       2
SYS_AI_6bssjp2f127a6      X1                       3
 
 
AI has create new index (SYS_AI_6bssjp2f127a6) with the column in X3, X2 and X1 order, as this index is able to service all the current known sql predicate combinations. 
 
WHERE X3 = ?? and X2 = ?? and X1 = ??
WHERE X3 = ?? and X2 = ??
WHERE X3 = ??
 
As the leading column in the index is listed in all three current scenarios. This means the previous index (SYS_AI_0jbgd6ymtua6u) is now redundant as this new index can service all the know SQL Predicate combinations, as a result AI drops that redundant index.
 
This was a nice capability of AI, the ability to effectively reorder the columns within the index based on the new workloads.