Automatic Indexing - Part IX

In this blog post we will see about how Automatic Indexing (AI) will handle scenarios related to existing manually created indexes.
 
To investigate let’s create a table
 
ai_demo@PDB19> create table DEMO009b
  2  nologging as
  3  select rownum as id,
  4     mod(rownum,1000)+1 as x1,
  5     ceil(dbms_random.value(0,100)) as x2,
  6     ceil(dbms_random.value(0,10)) as x3,
  7     a.object_name
  8  from all_objects a,
  9        all_users, all_users
 10  where rownum <=10000000
 11  order by dbms_random.value ;
 
Table created.
 
ai_demo@PDB19> begin
  2     dbms_output.put_line(
  3                dbms_stats.create_extended_stats(user,'DEMO009b','(X2,X3)')
  4                );
  5  end;
  6  /
SYS_STU$LBMUZV7AA4J6YKVU6ZFPZC
 
ai_demo@PDB19> begin
  2     dbms_stats.gather_table_stats(user,'DEMO009b',
  3         no_invalidate=>false,
  4         method_opt=>'for all columns size auto for all hidden columns size 2048',
  5         degree=>8 );
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
ai_demo@PDB19> select column_name,num_distinct,num_nulls,density
  2  from user_tab_cols
  3  where table_name ='DEMO009B'
  4  and ( column_name like 'X%'
  5     or column_name like 'ID' )
  6  order by column_name;
 
COLUMN_NAM NUM_DISTINCT  NUM_NULLS    DENSITY
---------- ------------ ---------- ----------
ID              9914368          0 1.0086E-07
X1                 1000          0       .001
X2                  100          0        .01
X3                   10          0         .1
 
However, in this demo, going to create a manual index but with the column list X3, X2 order. This is the opposite order in which a default AI would be created ( X2, X3 ) as this is the order of the columns in the table definition:
 
ai_demo@PDB19> create index demo009b_idx1
  2  on demo009b( x3,x2 )
  3  nologging ;
 
Index created.
 
ai_demo@PDB19> select index_name,auto,tablespace_name,compression,
  2         status,visibility,indexing
  3  from user_indexes
  4  where table_name ='DEMO009B';
 
INDEX_NAME                AUT TABLESPACE_NAME           COMPRESSION   STATUS   VISIBILIT INDEXIN
------------------------- --- ------------------------- ------------- -------- --------- -------
DEMO009B_IDX1             NO  TS_INDEX_DEMO             DISABLED      VALID    VISIBLE   FULL
 
If we run the following query with filter predicates on these two indexed columns:
 
ai_demo@PDB19> set autotrace traceonly exp
ai_demo@PDB19> select max(x1), count(*) from demo009b where x2 = 20 and x3 = 3;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2919176907
 
------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |               |     1 |    10 |  9234   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE                      |               |     1 |    10 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| DEMO009B      | 10058 |    98K|  9234   (1)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | DEMO009B_IDX1 | 10058 |       |    26   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("X3"=3 AND "X2"=20)
 
ai_demo@PDB19> set autotrace off
 
The CBO decides to use the available index as it deems it too efficient than the full scan for the resultant of 10058 rows.
 
But what will AI do now, it we wait for 15 minute period until the next AI period and look at the resultant AI report:

INDEX DETAILS
-------------------------------------------------------------------------------
1. The following indexes were created:
-------------------------------------------------------------------------------
---------------------------------------------------------------------------
| Owner   | Table    | Index                | Key   | Type   | Properties |
---------------------------------------------------------------------------
| AI_DEMO | DEMO009B | SYS_AI_7mx8mdmfk2saq | X2,X3 | B-TREE | NONE       |
---------------------------------------------------------------------------
-------------------------------------------------------------------------------
 
AI has created a new index based on the column list X2 and X3, because it considers such an index would improve performance of the query.
 
However, it has recognized that the existing manual index based on X3 and X2 would have done preciously the same job.
 
ai_demo@PDB19> select owner,index_name,tablespace_name,compression,
  2         status,visibility,indexing,auto
  3  from all_indexes
  4  where table_name ='DEMO009B'
  5  and owner ='AI_DEMO';
 
OWNER      INDEX_NAME                TABLESPACE_NAME           COMPRESSION   STATUS   VISIBILIT INDEXIN AUT
---------- ------------------------- ------------------------- ------------- -------- --------- ------- ---
AI_DEMO    DEMO009B_IDX1             TS_INDEX_DEMO             DISABLED      VALID    VISIBLE   FULL    NO
AI_DEMO    SYS_AI_7mx8mdmfk2saq      TS_INDEX_DEMO             ADVANCED LOW  VALID    INVISIBLE FULL    YES
 
 
ai_demo@PDB19> select index_name,column_name,column_position
  2  from user_ind_columns
  3  where table_name ='DEMO009B'
  4  order by 1,3;
 
INDEX_NAME                COLUMN_NAM COLUMN_POSITION
------------------------- ---------- ---------------
DEMO009B_IDX1             X3                       1
DEMO009B_IDX1             X2                       2
SYS_AI_7mx8mdmfk2saq      X2                       1
SYS_AI_7mx8mdmfk2saq      X3                       2
 
 
AI was able to recognize that we already had an equivalent manually created index and so now, we have TWO indexes with one being visible to the optimizer and other being invisible to it and over the period of time, this “invisible” AI will be cleaned up by the AI process.