jump to navigation

“Hidden” Efficiencies of Non-Partitioned Indexes on Partitioned Tables Part IV” (Hallo Spaceboy) October 31, 2018

Posted by Richard Foote in Global Indexes, Local Indexes, Oracle Indexes, Partitioned Indexes.
trackback

Hallo Spaceboy

In Part I, Part II and Part III we looked at some advantages of Global Indexes that may not be obvious to some.

One of the advantages of a Local Index vs. Non-Partitioned Global Index is that a Local Index being a smaller index structures may have a reduced BLEVEL in comparison. This can save a logical read each and every time the index is accessed.

However, if this is a performance concern for usage of a corresponding Global Index, this is a key reason why Global Indexes can likewise be partitioned.

As we saw in the demo in Part III, when the Global Index is used in a query that uses a predicate with the table partitioned key:

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

The query required 5 consistent gets.

But when the Local Index is used with a reduced BLEVEL:

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

The query used just 4 consistent gets (vs. 5) as a result of the reduction of 1 for the BLEVEL.

So to have the best of both worlds, excellent performance when the query doesn’t contain the table partitioned columns in a predicate and excellent performance to match Local Indexes when the table partitioned key is specified, a Global Index can also be partitioned into many, smaller index structures.

However, unlike a Local Index, a Global Partitioned Index can be partitioned in a manner totally different to that of the table (indeed, the table doesn’t even have to be partitioned).

In this example, the Global Index on the TOTAL_SALES column is partitioned based on TOTAL_SALES (unlike the table which is partitioned based on RELEASE_DATE) and partitioned into 16 partitions (unlike the table which has 8 partitions):

SQL> CREATE INDEX big_bowie_total_sales_global_i ON big_bowie(total_sales)
2 GLOBAL PARTITION BY RANGE (total_sales)
3 (PARTITION P1 VALUES LESS THAN (12501),
4 PARTITION P2 VALUES LESS THAN (25001),
5 PARTITION P3 VALUES LESS THAN (37501),
6 PARTITION P4 VALUES LESS THAN (50001),
7 PARTITION P5 VALUES LESS THAN (62501),
8 PARTITION P6 VALUES LESS THAN (75001),
9 PARTITION P7 VALUES LESS THAN (87501),
10 PARTITION P8 VALUES LESS THAN (100001),
11 PARTITION P9 VALUES LESS THAN (112501),
12 PARTITION P10 VALUES LESS THAN (125001),
13 PARTITION P11 VALUES LESS THAN (137501),
14 PARTITION P12 VALUES LESS THAN (150001),
15 PARTITION P13 VALUES LESS THAN (162501),
16 PARTITION P14 VALUES LESS THAN (175001),
17 PARTITION P15 VALUES LESS THAN (187501),
18 PARTITION P16 VALUES LESS THAN (MAXVALUE)) invisible;

Index created.

SQL> select index_name, partition_name, blevel, leaf_blocks

from dba_ind_partitions where index_name='BIG_BOWIE_TOTAL_SALES_GLOBAL_I';

INDEX_NAME                     PARTITION_NAME           BLEVEL LEAF_BLOCKS
------------------------------ -------------------- ---------- -----------
BIG_BOWIE_TOTAL_SALES_GLOBAL_I P1                            1         335
BIG_BOWIE_TOTAL_SALES_GLOBAL_I P10                           1         349
BIG_BOWIE_TOTAL_SALES_GLOBAL_I P11                           1         349
BIG_BOWIE_TOTAL_SALES_GLOBAL_I P12                           1         349
BIG_BOWIE_TOTAL_SALES_GLOBAL_I P13                           1         349
BIG_BOWIE_TOTAL_SALES_GLOBAL_I P14                           1         349
BIG_BOWIE_TOTAL_SALES_GLOBAL_I P15                           1         349
BIG_BOWIE_TOTAL_SALES_GLOBAL_I P16                           1         349
BIG_BOWIE_TOTAL_SALES_GLOBAL_I P2                            1         349
BIG_BOWIE_TOTAL_SALES_GLOBAL_I P3                            1         349
BIG_BOWIE_TOTAL_SALES_GLOBAL_I P4                            1         349
BIG_BOWIE_TOTAL_SALES_GLOBAL_I P5                            1         349
BIG_BOWIE_TOTAL_SALES_GLOBAL_I P6                            1         349
BIG_BOWIE_TOTAL_SALES_GLOBAL_I P7                            1         349
BIG_BOWIE_TOTAL_SALES_GLOBAL_I P8                            1         349
BIG_BOWIE_TOTAL_SALES_GLOBAL_I P9                            1         349

We notice that each Global Index partition now only has a BLEVEL of 1, the same as the corresponding Local Index.

As such, the performance of the Global Index now matches that of the Local Index when the table partition key is referenced in an SQL predicate:

SQL> alter index BIG_BOWIE_TOTAL_SALES_LOCAL_I invisible;

Index altered.

SQL> alter index BIG_BOWIE_TOTAL_SALES_GLOBAL_I visible;

Index altered.

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: 2458305506

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

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

2 - filter("RELEASE_DATE"=TO_DATE(' 2017-01-01 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

 

So Global Indexes can perform optimally, regardless of whether the table partition key is specified in a predicate or not.

The same can’t always be said for a corresponding Local Index.

Comments»

1. vaurob - November 21, 2018

Dear Richard

I’m a big fan of this blog. Each time I read a post I learn something. Thanks for your time sharing these insights.

From this most recent “Hidden efficiencies…” series one could conclude that Global Indexes are like silver bullets. So please share the cons regarding them, so that we are in better position when designing indexing for partitioned tables. Even if those cons are not performance related it would be nice to have a more complete picture.

Thanks again,
RobK

Like

Reply
2. Richard Foote - January 15, 2019

Hi RobK

No, Global Indexes most certainly are not silver bullets, they’re just a capability that are sometimes not used when they should be 🙂 The main “cons” with Global Indexes are that they can be more troublesome and expensive to maintain during table partition operations such as split/merge/drop and that they’re potentially larger objects (and hence maybe more expensive to access) than maybe smaller, local indexes.

Both Global/Local indexes have their place, it’s just a question of using the right index in the right scenarios.

Like

Reply
3. Pete - March 17, 2019

Very useful to know, thanks for sharing

Like

Reply
4. Aramis Ferreira Marques - October 28, 2021

Hi Richard,

When do we add the fact that the index is INDEXING PARTIAL or FULL in the context of the article?

What points are important?

Thanks.

Like

Reply

Leave a comment