“Hidden” Efficiencies of Non-Partitioned Indexes on Partitioned Tables Part III” (Ricochet)

In Part I and Part II of this series, we looked at how Global Indexes can effectively perform “Partition Pruning” when the partition keys are specified in SQL predicates, by only using those index entries that have a Data Object of interest stored within the index Rowids.

In this piece, I’ll cover the key performance advantage that Global Indexes have over Local Indexes and why I generally recommended Global Indexes from a purely performance perspective.

First, a quick recap of how the Global Index performed. Following is the performance of a query where the table partitioned key is specified in the query:

SQL> SELECT * FROM big_bowie
WHERE total_sales = 42 and
      release_date between '01-JAN-2017' and '31-JUL-2017';

        ID   ALBUM_ID COUNTRY_ID RELEASE_D TOTAL_SALES
---------- ---------- ---------- --------- -----------
   1000041         42         42 20-JUL-17          42

Execution Plan
----------------------------------------------------------
Plan hash value: 1081241859

--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                                  | Name                    | Rows | Bytes | Cost (%CPU) | Time     | Pstart | Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                           |                         |    1 |    25 |      13 (0) | 00:00:01 |        |       |
|* 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | BIG_BOWIE               |    1 |    25 |      13 (0) | 00:00:01 |      7 |     7 |
|* 2 | INDEX RANGE SCAN                           | BIG_BOWIE_TOTAL_SALES_I |   10 |       |       3 (0) | 00:00:01 |        |       |
--------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("RELEASE_DATE"=TO_DATE('2017-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
2 - access("TOTAL_SALES"=42)

Statistics
----------------------------------------------------------
  0 recursive calls
  0 db block gets
  5 consistent gets
  0 physical reads
  0 redo size
885 bytes sent via SQL*Net to client
624 bytes received via SQL*Net from client
  2 SQL*Net roundtrips to/from client
  0 sorts (memory)
  0 sorts (disk)
  1 rows processed

As discussed previously, at just 5 consistent gets, it’s very efficient as only the table blocks that reside in possible partitions of interest are only accessed.

The following query selects all TOTAL_SALES values of interest, with no partition key predicate:

SQL> SELECT * FROM big_bowie
WHERE total_sales = 42;

        ID   ALBUM_ID COUNTRY_ID RELEASE_D TOTAL_SALES
---------- ---------- ---------- --------- -----------
    400041         42         42 28-JAN-12          42
   1800041         42         42 28-JAN-12          42
    800041         42         42 03-MAR-13          42
   1200041         42         42 07-APR-14          42
   1600041         42         42 12-MAY-15          42
    200041         42         42 12-MAY-15          42
    600041         42         42 15-JUN-16          42
   1000041         42         42 20-JUL-17          42
        41         42         42 24-AUG-18          42
   1400041         42         42 24-AUG-18          42

10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1761527485

--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                                  | Name                    | Rows | Bytes | Cost (%CPU) | Time     | Pstart | Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                           |                         |   10 |   250 |      13 (0) | 00:00:01 |        |       |
|  1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | BIG_BOWIE               |   10 |   250 |      13 (0) | 00:00:01 |  ROWID | ROWID |
|* 2 | INDEX RANGE SCAN                           | BIG_BOWIE_TOTAL_SALES_I |   10 |       |       3 (0) | 00:00:01 |        |       |
--------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("TOTAL_SALES"=42)

Statistics
----------------------------------------------------------
   0 recursive calls
   0 db block gets
  14 consistent gets
   0 physical reads
   0 redo size
1184 bytes sent via SQL*Net to client
 624 bytes received via SQL*Net from client
   2 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
  10 rows processed

So the index is scanned (4 consistent gets) and 10 consistent gets for the 10 rows accessed (as the clustering here is poor) for a total of 14 consistent gets.

Let’s now compare this to an equivalent Local Index.

SQL> create index big_bowie_total_sales_local_i
on big_bowie(total_sales) local invisible;

Index created.

SQL> alter index big_bowie_total_sales_i invisible;

Index altered.

SQL> alter index big_bowie_total_sales_local_i visible;

Index altered.

If we compare the size characteristics between the two indexes we notice a couple of important differences:

SQL> select index_name, blevel, leaf_blocks from dba_indexes where table_name='BIG_BOWIE';

INDEX_NAME                         BLEVEL LEAF_BLOCKS
------------------------------ ---------- -----------
BIG_BOWIE_TOTAL_SALES_I                 2        5585
BIG_BOWIE_TOTAL_SALES_LOCAL_I           1        4444

SQL> select index_name, partition_name, blevel, leaf_blocks
from dba_ind_partitions where index_name='BIG_BOWIE_TOTAL_SALES_LOCAL_I';

INDEX_NAME                     PARTITION_NAME           BLEVEL LEAF_BLOCKS
------------------------------ -------------------- ---------- -----------
BIG_BOWIE_TOTAL_SALES_LOCAL_I  ALBUMS_2011                   1         525
BIG_BOWIE_TOTAL_SALES_LOCAL_I  ALBUMS_2012                   1         581
BIG_BOWIE_TOTAL_SALES_LOCAL_I  ALBUMS_2013                   1         579
BIG_BOWIE_TOTAL_SALES_LOCAL_I  ALBUMS_2014                   1         579
BIG_BOWIE_TOTAL_SALES_LOCAL_I  ALBUMS_2015                   1         579
BIG_BOWIE_TOTAL_SALES_LOCAL_I  ALBUMS_2016                   1         581
BIG_BOWIE_TOTAL_SALES_LOCAL_I  ALBUMS_2017                   1         580
BIG_BOWIE_TOTAL_SALES_LOCAL_I  ALBUMS_2018                   1         440

8 rows selected.

The first difference is that the corresponding Local index segments have a reduced BLEVEL (just 1) when compared to the Global Index (value of 2). A reduction in BLEVEL is quite possible as instead of one “big” index segment, we now have 8 “smaller” index segments.

However, if we look at the overall size of both indexes, we notice that the Local Index (at 4444 leaf blocks) is somewhat smaller than the Global Index (5585 leaf blocks). This is due to the Rowids of Local Indexes not having to be the extended Global Index 10 byte version (which contains the 4 byte Data Object Id), but the standard 6 byte version. Local Indexes can only reference the one table partition and so it’s unnecessary to store the corresponding Data Object Id within the Rowid.

A partial block dump of a Local Index leaf block:
Leaf block dump
===============
header address 924483684=0x371a8064
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 483
kdxcofbo 1002=0x3ea
kdxcofeo 1823=0x71f
kdxcoavs 821
kdxlespl 0
kdxlende 0
kdxlenxt 29412237=0x1c0cb8d
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8024] flag: ——-, lock: 0, len=12
col 0; len 2; (2): c1 47
col 1; len 6; (6): 01 c0 20 7b 00 a6
row#1[8012] flag: ——-, lock: 0, len=12
col 0; len 2; (2): c1 47
col 1; len 6; (6): 01 c0 22 3a 00 00
row#2[8000] flag: ——-, lock: 0, len=12
col 0; len 2; (2): c1 48
col 1; len 6; (6): 01 c0 20 7b 00 a7

Shows that the Rowids are only 6 bytes.

If we re-run the query that references the partition key in a SQL predicate:

SQL> SELECT * FROM big_bowie
WHERE total_sales = 42 and
      release_date between '01-JAN-2017' and '31-JUL-2017';

        ID   ALBUM_ID COUNTRY_ID RELEASE_D TOTAL_SALES
---------- ---------- ---------- --------- -----------
   1000041         42         42 20-JUL-17          42

Execution Plan
----------------------------------------------------------
Plan hash value: 3499166408

--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                                 | Name                          | Rows | Bytes | Cost (%CPU) | Time     | Pstart | Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                          |                               |    1 |    25 |       2 (0) | 00:00:01 |        |       |
|  1 | PARTITION RANGE SINGLE                    |                               |    1 |    25 |       2 (0) | 00:00:01 |      7 |     7 |
|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | BIG_BOWIE                     |    1 |    25 |       2 (0) | 00:00:01 |      7 |     7 |
|* 3 | INDEX RANGE SCAN                          | BIG_BOWIE_TOTAL_SALES_LOCAL_I |    1 |       |       1 (0) | 00:00:01 |      7 |     7 |
--------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("RELEASE_DATE"<=TO_DATE(' 2017-07-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
3 - access("TOTAL_SALES"=42)

Statistics
----------------------------------------------------------
  0 recursive calls
  0 db block gets
  4 consistent gets
  0 physical reads
  0 redo size
885 bytes sent via SQL*Net to client
624 bytes received via SQL*Net from client
  2 SQL*Net roundtrips to/from client
  0 sorts (memory)
  0 sorts (disk)
  1 rows processed

We notice that this is slightly more efficient with only 4 consistent gets, when previously the Global Index required 5 consistent gets. This is directly due to the reduction in the BLEVEL.

So this is a good thing, especially if this query is frequently executed.

If we now run the query without the partition key SQL predicate:

SQL> SELECT * FROM big_bowie WHERE total_sales = 42;

        ID   ALBUM_ID COUNTRY_ID RELEASE_D TOTAL_SALES
---------- ---------- ---------- --------- -----------
    400041         42         42 28-JAN-12          42
   1800041         42         42 28-JAN-12          42
    800041         42         42 03-MAR-13          42
   1200041         42         42 07-APR-14          42
   1600041         42         42 12-MAY-15          42
    200041         42         42 12-MAY-15          42
    600041         42         42 15-JUN-16          42
   1000041         42         42 20-JUL-17          42
        41         42         42 24-AUG-18          42
   1400041         42         42 24-AUG-18          42

10 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3527547124

--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                                 | Name                          | Rows | Bytes | Cost (%CPU) | Time     | Pstart | Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                          |                               |   10 |   250 |      15 (0) | 00:00:01 |        |       |
|  1 | PARTITION RANGE ALL                       |                               |   10 |   250 |      15 (0) | 00:00:01 |      1 |     8 |
|  2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | BIG_BOWIE                     |   10 |   250 |      15 (0) | 00:00:01 |      1 |     8 |
|* 3 | INDEX RANGE SCAN                          | BIG_BOWIE_TOTAL_SALES_LOCAL_I |   10 |       |       9 (0) | 00:00:01 |      1 |     8 |
--------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("TOTAL_SALES"=42)

Statistics
----------------------------------------------------------
   0 recursive calls
   0 db block gets
  27 consistent gets
   0 physical reads
   0 redo size
1088 bytes sent via SQL*Net to client
 624 bytes received via SQL*Net from client
   2 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
  10 rows processed

We notice that consistent gets have increased more significantly, up to 27 consistent gets when it was previously 14 consistent gets.

This is because instead of accessing the one Global Index structure, we are now forced to access all 8 Local index structures, as the required TOTAL_SALES value could potentially be found in any of the table partitions. So that’s a minimum of at least 2 consistent gets per Local Index (with an index of BLEVEL 1) that has to accessed even if there are actually no corresponding rows of interest in the particular table partition.

Imagine if this table had a 1000+ table partitions, you can easily see how the cost of using such Local Indexes can quickly become excessive.

So Local Indexes can be very problematic if the partition key is NOT referenced in the SQL or if the range of possible table partitions is excessive. The advantage of a Non-Partitioned index is that there is only the one index structure that need be accessed, regardless of the number of table partitions.

So what if you want to protect yourself from the possible ramifications of the table partition key not being referenced in SQL predicates, but you want to take advantage of the performance benefits of smaller index structures that might have a reduced index BLEVEL?

That’s the topic of Part IV in this series 🙂