Automatic Indexing - Part V

In the previous blog post, we explored some options that oracle might adopt when ordering the columns within the automatic index (AI) by default.
 
A point worth making is that if all columns of an index are specified within the sql equality predicates, then ordering the columns within the index is of little consequences,  lets explore if perhaps the resulting clustering factor of an index might be a factor in the default AI column order.
 
We will begin by creating a table that has two columns of interest X1 having highly clustered and X2 being poorly clustered.
 
ai_demo@PDB19> create table DEMO005c
  2  nologging as
  3  select 0 as id, 0 as x2,
  4        0 as x1, a.*
  5  from all_objects a
  6  where 1 = 0 ;
 
Table created.
 
ai_demo@PDB19> begin
  2        for c in 1..10000
  3        loop
  4              insert into DEMO005c
  5              select rownum, mod(rownum,100)+1, c, a.*
  6              from all_objects a
  7              where rownum <=100;
  8        end loop;
  9        dbms_stats.gather_table_stats(user,'DEMO005C');
 10  end;
 11  /
 
PL/SQL procedure successfully completed.
 
ai_demo@PDB19>
 
we then run the following query in which to hopefully create an AI on both (x1 and x2) columns
 
ai_demo@PDB19> select max(object_id)
  2  from DEMO005C
  3  where x2 = 83 and x1 = 404 ;
 
MAX(OBJECT_ID)
--------------
            83
 
We then wait for the AI to be created and checkout the AI report.
 
INDEX DETAILS
-------------------------------------------------------------------------------
1. The following indexes were created:
-------------------------------------------------------------------------------
---------------------------------------------------------------------------
| Owner   | Table    | Index                | Key   | Type   | Properties |
---------------------------------------------------------------------------
| AI_DEMO | DEMO005C | SYS_AI_fr47mu0cabc3n | X2,X1 | B-TREE | NONE       |
---------------------------------------------------------------------------
-------------------------------------------------------------------------------
VERIFICATION DETAILS
-------------------------------------------------------------------------------
1. The performance of the following statements improved:
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
 Parsing Schema Name  : AI_DEMO
 SQL ID               : 8zn2dvtp5k6t6
 SQL Text             : select max(object_id) from DEMO005C where x2 = 83 and
                      x1 = 404
 Improvement Factor   : 16502.5x
 
Execution Statistics:
-----------------------------
                    Original Plan                 Auto Index Plan
                    ----------------------------  ----------------------------
 Elapsed Time (s):  145763                        1281
 CPU Time (s):      137527                        1316
 Buffer Gets:       33013                         4
 Optimizer Cost:    4689                          4
 Disk Reads:        0                             2
 Direct Writes:     0                             0
 Rows Processed:    2                             1
 Executions:        2                             1
 
 
PLANS SECTION
---------------------------------------------------------------------------------------------
 
- Original
-----------------------------
 Plan Hash Value  : 3056910306
 
---------------------------------------------------------------------------------
| Id | Operation                    | Name     | Rows | Bytes | Cost | Time     |
---------------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |          |      |       | 4689 |          |
|  1 |   SORT AGGREGATE             |          |    1 |    11 |      |          |
|  2 |    TABLE ACCESS STORAGE FULL | DEMO005C |    1 |    11 | 4689 | 00:00:01 |
---------------------------------------------------------------------------------
 
- With Auto Indexes
-----------------------------
 Plan Hash Value  : 4049037473
 
--------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                 | Rows | Bytes | Cost | Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                      |    1 |    11 |    4 | 00:00:01 |
|   1 |   SORT AGGREGATE                       |                      |    1 |    11 |      |          |
|   2 |    TABLE ACCESS BY INDEX ROWID BATCHED | DEMO005C             |    3 |    33 |    4 | 00:00:01 |
| * 3 |     INDEX RANGE SCAN                   | SYS_AI_fr47mu0cabc3n |    1 |       |    3 | 00:00:01 |
--------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("X2"=83 AND "X1"=404)
 
We noticed that the index was created in X2 and X1 column order.
 
ai_demo@PDB19> select index_name,column_name,column_position
  2  from user_ind_columns
  3  where table_name ='DEMO005C'
  4  order by 1,3;
 
INDEX_NAME           COLUMN_NAM COLUMN_POSITION
-------------------- ---------- ---------------
SYS_AI_fr47mu0cabc3n X2                       1
SYS_AI_fr47mu0cabc3n X1                       2
 
If we create a manual index with the column order reversed.
 
ai_demo@PDB19> create index DEMO005c_idx on
  2  DEMO005c( x1,x2 )
  3  nologging;
 
Index created.
 
ai_demo@PDB19> select i.owner,i.index_name,i.clustering_factor,t.blocks,t.num_rows
  2  from all_indexes i,
  3      all_tables t
  4  where i.table_name ='DEMO005C'
  5  and i.owner = user
  6  and t.table_name = i.table_name
  7  and t.owner = i.table_owner;
 
OWNER      INDEX_NAME                CLUSTERING_FACTOR     BLOCKS   NUM_ROWS
---------- ------------------------- ----------------- ---------- ----------
AI_DEMO    SYS_AI_fr47mu0cabc3n                1000000      17237    1000000
AI_DEMO    DEMO005C_IDX                          35997      17237    1000000
 
 
We noticed the manual index got better clustering factor, so clustering factor doesn’t appear to be the factor in AI column order. After creating many many AI under all sorts of different scenarios, the default behavior is for Oracle to create AI in column id order (the order in which the fields are defined in the table definition)