Automatic Indexing - Part XXI (Partitioned index)

Here we are going to discuss about how Automatic indexing (AI) works in relation with partitioned objects. Oracle has various options when indexing a partitioned table
 

• Non-partitioned index
• Globally partitioned index
• Locally partitioned index 

So that question(s) are how AI handles scenario with partitioned objects? To answer that we are going to build an interval partitioned table with a partition for each year worth of data.
 
ai_demo@PDB1> create table demo023
  2  partition by range( release_dt )
  3  interval ( numtoyminterval(1,'year') )
  4  ( partition p2010 values less than
  5         (to_date('01-jan-2011','dd-mon-yyyy')) )
  6  nologging as
  7  select a.*,
  8         sysdate - mod(rownum,2800) as release_dt ,
  9         ceil( dbms_random.value(1,500000) ) as sales_amt
 10  from stage a, stage b, stage c
 11  where rownum <= 10000000 ;
 
Table created.
 
We will now run the following very selective queries based on the “sales_amt” column that is not part of partitioning key column.
 
ai_demo@PDB1> select * from demo023 where sales_amt = 55;
 
25 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3128466500
 
------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |         |    20 |  2160 | 46565   (1)| 00:00:02 |       |       |
|   1 |  PARTITION RANGE ALL       |         |    20 |  2160 | 46565   (1)| 00:00:02 |     1 |1048575|
|*  2 |   TABLE ACCESS STORAGE FULL| DEMO023 |    20 |  2160 | 46565   (1)| 00:00:02 |     1 |1048575|
------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - storage("SALES_AMT"=55)
       filter("SALES_AMT"=55)
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     169513  consistent gets
     169495  physical reads
          0  redo size
       2644  bytes sent via SQL*Net to client
        546  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         25  rows processed
 
 
Without an index, the CBO has no choice but to use a Full table scan.
 
But what will AI make of things? If we look at the next AI report, it shows this.
 
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
 Index candidates                              : 3
 Indexes created (visible / invisible)         : 2 (2 / 0)
 Space used (visible / invisible)              : 327.16 MB (327.16 MB / 0 B)
 Indexes dropped                               : 0
 SQL statements verified                       : 3
 SQL statements improved (improvement factor)  : 2 (19304.5x)
 SQL plan baselines created                    : 0
 Overall improvement factor                    : 16337.2x
-------------------------------------------------------------------------------
 
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 | DEMO020A | SYS_AI_6z61w0j7jqqc0 | X2        | B-TREE | NONE       |
| AI_DEMO | DEMO023  | SYS_AI_g4dwbmutt3xqu | SALES_AMT | B-TREE | NONE       |
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
 Parsing Schema Name  : AI_DEMO
 SQL ID               : 8kr62r0f9f3qc
 SQL Text             : select * from demo023 where sales_amt = 55
 Improvement Factor   : 18844.2x
 
Execution Statistics:
-----------------------------
                    Original Plan                 Auto Index Plan
                    ----------------------------  ----------------------------
 Elapsed Time (s):  2605642                       5137
 CPU Time (s):      285852                        2215
 Buffer Gets:       508796                        28
 Optimizer Cost:    46565                         28
 Disk Reads:        508491                        27
 Direct Writes:     0                             0
 Rows Processed:    75                            25
 Executions:        3                             1
 
 
PLANS SECTION
---------------------------------------------------------------------------------------------
 
- Original
-----------------------------
 Plan Hash Value  : 3128466500
 
---------------------------------------------------------------------------------
| Id | Operation                    | Name    | Rows | Bytes | Cost  | Time     |
---------------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |         |      |       | 46565 |          |
|  1 |   PARTITION RANGE ALL        |         |   20 |  2160 | 46565 | 00:00:02 |
|  2 |    TABLE ACCESS STORAGE FULL | DEMO023 |   20 |  2160 | 46565 | 00:00:02 |
---------------------------------------------------------------------------------
 
- With Auto Indexes
-----------------------------
 Plan Hash Value  : 1036907592
 
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name                 | Rows | Bytes | Cost | Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |                      |   25 |  2700 |   28 | 00:00:01 |
|   1 |   TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | DEMO023              |   25 |  2700 |   28 | 00:00:01 |
| * 2 |    INDEX RANGE SCAN                          | SYS_AI_g4dwbmutt3xqu |   25 |       |    3 | 00:00:01 |
--------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("SALES_AMT"=55)
 
 
Notes
-----
- Dynamic sampling used for this statement ( level = 11 )
 
 
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
 
 
We notice a couple of interesting points, first – yes AI has created an index based on “sales_amt” column (SYS_AI_g4dwbmutt3xqu) as it improves the performance by a reported 18844.2x. also the AI is non-partitioned (global) index. From a performance perspective, this is the most effective index to create to improve the performance of the query as the CBO only has the one index structure to navigate (vs. LOCAL index that would require having to range scan down all the index structures of each table partition)
 
If we look at the index details
 
ai_demo@PDB1> 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
 
 
We noticed that it is a non-partitioned index, that is VISIBLE and VALID and can potentially used by any database session.
 
If we now try to run a query
 
ai_demo@PDB1> set autotrace traceonly exp statistics
ai_demo@PDB1> select * from demo023 where sales_amt = 55;
 
25 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1036907592
 
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                      |    20 |  2160 |    23   (0)| 00:00:01 |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| DEMO023              |    20 |  2160 |    23   (0)| 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX RANGE SCAN                         | SYS_AI_g4dwbmutt3xqu |    20 |       |     3   (0)| 00:00:01 |       |       |
-----------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("SALES_AMT"=55)
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         29  consistent gets
          0  physical reads
          0  redo size
       2644  bytes sent via SQL*Net to client
        546  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         25  rows processed
 
ai_demo@PDB1> set autotrace off
 
 
we can see that the query now uses the newly created AI and is indeed more efficient, performing now just 29 logical IO (previously it was 169K logical IO)