Automatic Indexing - Part XXII (Partitioned index)

In the previous post we discussed, how AI can now create a non-partitioned index, if deemed to be the most effective index structure, a non-partitioned index is indeed likely the most effective index structure if the underlying table has many partitions and the associated SQL equality predicates only reference non-partitioned key columns. A non-partitioned index ensure Oracle only need to scan the single index structure and not all the partitions of the local index.
 
But what if got SQL that reference the column by which the underlying table is partitioned?
 
The following SQL has an equality predicate on the RELEASE_DATE column, the column by which the table got partitioned. 
 
ai_demo@PDB19> set autotrace traceonly exp statistics
ai_demo@PDB19> select * from demo023 where release_dt = sysdate;
 
no rows selected
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2198382565
 
------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |         |  3517 |   370K|  2878   (1)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE    |         |  3517 |   370K|  2878   (1)| 00:00:01 |   KEY |   KEY |
|*  2 |   TABLE ACCESS STORAGE FULL| DEMO023 |  3517 |   370K|  2878   (1)| 00:00:01 |   KEY |   KEY |
------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - storage("RELEASE_DT"=SYSDATE@!)
       filter("RELEASE_DT"=SYSDATE@!)
 
 
if we look at the subsequent AI report:

 
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
 Index candidates                              : 2
 Indexes created (visible / invisible)         : 1 (1 / 0)
 Space used (visible / invisible)              : 137.36 MB (137.36 MB / 0 B)
 Indexes dropped                               : 0
 SQL statements verified                       : 1
 SQL statements improved (improvement factor)  : 1 (10504.7x)
 SQL plan baselines created                    : 0
 Overall improvement factor                    : 10504.7x
-------------------------------------------------------------------------------
 
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 | DEMO023 | SYS_AI_11muvn2pw0ym6 | RELEASE_DT | B-TREE | LOCAL      |
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
 
 
We noticed that AI has this instance created a LOCAL index. If we look further down in the AI report.
 
 
VERIFICATION DETAILS
-------------------------------------------------------------------------------
1. The performance of the following statements improved:
-------------------------------------------------------------------------------
 Parsing Schema Name  : AI_DEMO
 SQL ID               : ctv01z9wf6q7h
 SQL Text             : select * from demo023 where release_dt = sysdate
 Improvement Factor   : 10504.7x
 
Execution Statistics:
-----------------------------
                    Original Plan                 Auto Index Plan
                    ----------------------------  ----------------------------
 Elapsed Time (s):  786346                        1191
 CPU Time (s):      533290                        1582
 Buffer Gets:       31740                         3
 Optimizer Cost:    2878                          4
 Disk Reads:        10424                         2
 Direct Writes:     0                             0
 Rows Processed:    0                             0
 Executions:        3                             1
 
 
PLANS SECTION
---------------------------------------------------------------------------------------------
 
- Original
-----------------------------
 Plan Hash Value  : 2198382565
 
---------------------------------------------------------------------------------
| Id | Operation                    | Name    | Rows | Bytes  | Cost | Time     |
---------------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |         |      |        | 2878 |          |
|  1 |   PARTITION RANGE SINGLE     |         | 3517 | 379836 | 2878 | 00:00:01 |
|  2 |    TABLE ACCESS STORAGE FULL | DEMO023 | 3517 | 379836 | 2878 | 00:00:01 |
---------------------------------------------------------------------------------
 
- With Auto Indexes
-----------------------------
 Plan Hash Value  : 1782259668
 
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name                 | Rows | Bytes | Cost | Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |                      |    2 |   216 |    4 | 00:00:01 |
|   1 |   PARTITION RANGE SINGLE                     |                      |    2 |   216 |    4 | 00:00:01 |
|   2 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | DEMO023              |    2 |   216 |    4 | 00:00:01 |
| * 3 |     INDEX RANGE SCAN                         | SYS_AI_11muvn2pw0ym6 |    1 |       |    3 | 00:00:01 |
--------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("RELEASE_DT"=SYSDATE@!)
 
 
Notes
-----
- Dynamic sampling used for this statement ( level = 11 )
 
 
We can see AI has created the index because it provides an average improvement factor of 10504.7x, as the necessary indexed columns matches the table partition key, it makes sense for the associated index be a local index as Oracle is certain which specific index partition to visit based on the value of the equality predicate.
 
If we look at the details of this new AI
 
ai_demo@PDB19> select owner,index_name,indexing,auto,partitioned,
  2         status,visibility,clustering_factor,leaf_blocks
  3  from all_indexes
  4  where table_name ='DEMO023'
  5  and owner ='AI_DEMO';
 
OWNER      INDEX_NAME                INDEXIN AUT PAR STATUS   VISIBILIT CLUSTERING_FACTOR LEAF_BLOCKS
---------- ------------------------- ------- --- --- -------- --------- ----------------- -----------
AI_DEMO    SYS_AI_g4dwbmutt3xqu      FULL    YES NO  VALID    VISIBLE             9999423       21374
AI_DEMO    SYS_AI_11muvn2pw0ym6      FULL    YES YES N/A      VISIBLE            10000000       15374
 
ai_demo@PDB19> select index_name,column_name,column_position
  2  from user_ind_columns
  3  where table_name ='DEMO023'
  4  order by 1,3;
 
INDEX_NAME                COLUMN_NAM COLUMN_POSITION
------------------------- ---------- ---------------
SYS_AI_11muvn2pw0ym6      RELEASE_DT               1
SYS_AI_g4dwbmutt3xqu      SALES_AMT                1
 
ai_demo@PDB19> select ip.index_name, ip.partition_name, ip.status,ip.compression
  2  from user_ind_partitions ip,
  3     user_indexes i
  4  where ip.index_name = i.index_name
  5  and i.table_name ='DEMO023';
 
INDEX_NAME                PARTITION_NAME  STATUS   COMPRESSION
------------------------- --------------- -------- -------------
SYS_AI_11muvn2pw0ym6      P2010           USABLE   ADVANCED LOW
SYS_AI_11muvn2pw0ym6      SYS_P145010     USABLE   ADVANCED LOW
SYS_AI_11muvn2pw0ym6      SYS_P145011     USABLE   ADVANCED LOW
SYS_AI_11muvn2pw0ym6      SYS_P145012     USABLE   ADVANCED LOW
SYS_AI_11muvn2pw0ym6      SYS_P145013     USABLE   ADVANCED LOW
SYS_AI_11muvn2pw0ym6      SYS_P145014     USABLE   ADVANCED LOW
SYS_AI_11muvn2pw0ym6      SYS_P145015     USABLE   ADVANCED LOW
SYS_AI_11muvn2pw0ym6      SYS_P145016     USABLE   ADVANCED LOW
SYS_AI_11muvn2pw0ym6      SYS_P145017     USABLE   ADVANCED LOW
SYS_AI_11muvn2pw0ym6      SYS_P145018     USABLE   ADVANCED LOW
 
10 rows selected.
 
 
We can see that indeed a visible, usable, local index was created by AI. So depending on the column within the index AI can potentially create either a local or non-partitioned index when indexing a partitioned table.