Oracle 19c Automatic Indexing: Indexing Partitioned Tables Part II (Neighbourhood Threat)

In my first post on Automatic Indexing on Partitioned Tables, I discussed how Automatic Indexing (AI) can now create a Non-Partitioned index if deemed the most effective indexing structure (this wasn’t supported when AI was initially released). A Non-Partitioned index is indeed likely the most efficient indexing structure if the underlying table has many partitions and associated SQL equality predicates only reference non-partition key columns. A Non-Partitioned index ensure Oracle only needs to scan the single index structure and not all the partitions of a Local index.

But what if SQLs do reference the column by which the underlying table is partitioned?

The following SQL has an equality filtering predicate on the RELEASE_DATE column, the column by which the BIG_BOWIE1 table is partitioned:

SQL> SELECT * FROM big_bowie1 where release_date = to_date('2013-12-30 22:15:25', 'syyyy-mm-dd hh24:mi:ss');

no rows selected

If we look at the subsequent AI report:

INDEX DETAILS
-------------------------------------------------------------------------------
The following indexes were created:
*: invisible
-------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
| Owner | Table      | Index                | Key          | Type   | Properties |
------------------------------------------------------------------------------------------------
| BOWIE | BIG_BOWIE1 | SYS_AI_14gpurjp8m76s | RELEASE_DATE | B-TREE | LOCAL      |
------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------

 

We notice that Automatic Indexing has in this instance created a Local Index.

If we look further down the AI report:

-------------------------------------------------------------------------------
Parsing Schema Name : BOWIE
SQL ID              : 4mm3mbkk38pa8
SQL Text            : SELECT * FROM big_bowie1 where release_date = to_date('2013-12-30 22:15:25', 'syyyy-mm-dd hh24:mi:ss')
Improvement Factor  : 8339x

Execution Statistics:
-----------------------------
                  Original Plan                Auto Index Plan
                  ---------------------------- ----------------------------
Elapsed Time (s): 146957                       71
CPU Time (s):     146124                       71
Buffer Gets:      16678                        3
Optimizer Cost:   162                          4
Disk Reads:       0                            0
Direct Writes:    0                            0
Rows Processed:   0                            0
Executions:       2                            1

PLANS SECTION
---------------------------------------------------------------------------------------------

- Original
-----------------------------
Plan Hash Value : 4031749531

-----------------------------------------------------------------------------------
| Id | Operation                 | Name       | Rows | Bytes | Cost | Time        |
-----------------------------------------------------------------------------------
| 0  | SELECT STATEMENT          |            |      |       |  162 |             |
| 1  | PARTITION RANGE SINGLE    |            | 3602 | 93652 |  162 | 00:00:01    |
| 2  | TABLE ACCESS STORAGE FULL | BIG_BOWIE1 | 3602 | 93652 |  162 | 00:00:01    |
-----------------------------------------------------------------------------------

Notes
-----
- dop = 1
- px_in_memory_imc = no
- px_in_memory = no

- With Auto Indexes
-----------------------------
Plan Hash Value : 4049653350

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                 | Rows | Bytes | Cost | Time        |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                      |    3 |    78 |    4 | 00:00:01    |
|   1 | PARTITION RANGE SINGLE                    |                      |    3 |    78 |    4 | 00:00:01    |
|   2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | BIG_BOWIE1           |    3 |    78 |    4 | 00:00:01    |
| * 3 | INDEX RANGE SCAN                          | SYS_AI_14gpurjp8m76s |    1 |       |    3 | 00:00:01    |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("RELEASE_DATE"=TO_DATE(' 2013-12-30 22:15:25', 'syyyy-mm-dd hh24:mi:ss'))

Notes
-----
- Dynamic sampling used for this statement ( level = 11 )

 

We can see Automatic Indexing has created the index because it provides an average Improvement Factor of 8339x. As the necessary indexed column(s) matches the table partitioning 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:

SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor
from user_indexes where table_name='BIG_BOWIE1';

INDEX_NAME                     AUT CON VISIBILIT COMPRESSION   STATUS     NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
------------------------------ --- --- --------- ------------- -------- ---------- ----------- -----------------
SYS_AI_14gpurjp8m76s           YES NO  VISIBLE   ADVANCED LOW  N/A        20000000       30742          19941449
SYS_AI_8armv0hqq73fa           YES NO  VISIBLE   ADVANCED LOW  VALID      20000000       42697          19995451

SQL> select index_name, column_name, column_position from user_ind_columns where table_name='BIG_BOWIE1' order by index_name, column_position;

INDEX_NAME                     COLUMN_NAME     COLUMN_POSITION
------------------------------ --------------- ---------------
SYS_AI_14gpurjp8m76s           RELEASE_DATE                  1
SYS_AI_8armv0hqq73fa           TOTAL_SALES                   1

SQL> SELECT index_name, partitioning_type, partition_count, locality FROM user_part_indexes WHERE table_name = 'BIG_BOWIE1';

INDEX_NAME                     PARTITION PARTITION_COUNT LOCALI
------------------------------ --------- --------------- ------
SYS_AI_14gpurjp8m76s           RANGE                   8 LOCAL

SQL> select index_name, partition_name, status, compression from user_ind_partitions
where index_name in (select index_name from user_indexes where table_name='BIG_BOWIE1')
order by partition_position;

INDEX_NAME           PARTITION_NAME       STATUS   COMPRESSION
-------------------- -------------------- -------- -------------
SYS_AI_14gpurjp8m76s ALBUMS_2013          USABLE   ADVANCED LOW
SYS_AI_14gpurjp8m76s ALBUMS_2014          USABLE   ADVANCED LOW
SYS_AI_14gpurjp8m76s ALBUMS_2015          USABLE   ADVANCED LOW
SYS_AI_14gpurjp8m76s ALBUMS_2016          USABLE   ADVANCED LOW
SYS_AI_14gpurjp8m76s ALBUMS_2017          USABLE   ADVANCED LOW
SYS_AI_14gpurjp8m76s ALBUMS_2018          USABLE   ADVANCED LOW
SYS_AI_14gpurjp8m76s ALBUMS_2019          USABLE   ADVANCED LOW
SYS_AI_14gpurjp8m76s ALBUMS_2020          USABLE   ADVANCED LOW

 

We can see that indeed, a Visible, Usable, Local index was created by Automatic Indexing.

So depending on the column(s) within the index, Automatic Indexing can potentially create either a Local or Non-Partitioned index when indexing a partitioned table.