Automatic Indexing - Part IV

The next curious thing about automatic index (AI) was in which order would Oracle by default place the columns in case of composite index. This can be a crucial decision with respect to the effectiveness of the index, certainly one would expect the index column order dependent on the sql predicates running in the database, but the number of possible options that oracle might adopt when determining the default index column order are
 

•          Column name order
  
•          Column id order
  
•          Column cardinality order
  
•          Best clustering factor, or others
  

 
To investigate that started with a basic table having three columns with different levels of cardinality.
 
ai_demo@PDB19> create table demo004b1
  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,1000)+1 as x3,
  7         a.*
  8  from all_objects a,
  9                all_users
 10  where rownum <= 10000000;
 
Table created.
 
ai_demo@PDB19> select column_name,num_distinct,num_nulls,density
  2  from user_tab_cols
  3  where table_name ='DEMO004B1'
  4  and column_name like 'X%'
  5  order by column_name;
 
COLUMN_NAM NUM_DISTINCT  NUM_NULLS    DENSITY
---------- ------------ ---------- ----------
X1                   10          0         .1
X2                  100          0        .01
X3                 1000          0       .001
 
Then ran the following query with predicates based on three columns X1, X2 and X3
 
ai_demo@PDB19> select count(x3) from demo004b1
  2  where x3 = 57 and x2 = 57
  3  and x1 = 5;
 
 COUNT(X3)
----------
         0
 
If we look at the resulting AI
 
INDEX DETAILS
-------------------------------------------------------------------------------
1. The following indexes were created:
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
| Owner   | Table     | Index                | Key      | Type   | Properties |
-------------------------------------------------------------------------------
| AI_DEMO | DEMO004B1 | SYS_AI_02331gjcah6a7 | X1,X2,X3 | B-TREE | NONE       |
-------------------------------------------------------------------------------
 
ai_demo@PDB19> select index_name,column_name,column_position
  2  from user_ind_columns
  3  where table_name ='DEMO004B1'
  4  order by 1,3;
 
INDEX_NAME           COLUMN_NAM COLUMN_POSITION
-------------------- ---------- ---------------
SYS_AI_02331gjcah6a7 X1                       1
SYS_AI_02331gjcah6a7 X2                       2
SYS_AI_02331gjcah6a7 X3                       3
 
We noticed that the AI is in X1, X2, X3 order.
 
If we create a similar table, but this time have the columns with a different order of cardinality
 
ai_demo@PDB19> create table demo004b2
  2  nologging as
  3  select rownum as id,
  4         mod(rownum,1000)+1 as x1,
  5         mod(rownum,100)+1 as x2,
  6         mod(rownum,10)+1 as x3,
  7         a.*
  8  from all_objects a,
  9                all_users
 10  where rownum <= 10000000;
 
Table created.
 
ai_demo@PDB19> select column_name,num_distinct,num_nulls,density
  2  from user_tab_cols
  3  where table_name ='DEMO004B2'
  4  and column_name like 'X%'
  5  order by column_name;
 
COLUMN_NAM NUM_DISTINCT  NUM_NULLS    DENSITY
---------- ------------ ---------- ----------
X1                 1000          0       .001
X2                  100          0        .01
X3                   10          0         .1
 
ai_demo@PDB19> select count(x3) from demo004b2
  2  where x3 = 5 and x2 = 57
  3  and x1 = 57;
 
 COUNT(X3)
----------
         0
 
We notice that the resultant AI is still in the same order X1, X2 and X3 order.
 
INDEX DETAILS
-------------------------------------------------------------------------------
1. The following indexes were created:
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
| Owner   | Table     | Index                | Key      | Type   | Properties |
-------------------------------------------------------------------------------
| AI_DEMO | DEMO004B2 | SYS_AI_9q22520ry1ju1 | X1,X2,X3 | B-TREE | NONE       |
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
 
ai_demo@PDB19> select index_name,column_name,column_position
  2  from user_ind_columns
  3  where table_name ='DEMO004B2'
  4  order by 1,3;
 
INDEX_NAME           COLUMN_NAM COLUMN_POSITION
-------------------- ---------- ---------------
SYS_AI_9q22520ry1ju1 X1                       1
SYS_AI_9q22520ry1ju1 X2                       2
SYS_AI_9q22520ry1ju1 X3                       3
 
So we can eliminate the column cardinality as being a contributing factor in Oracle deciding in which manner to order the index key columns.