Index design I : Partitioned Index: Global or local?

Recently I was investigating a performance problem using the SQL Monitoring feature looking for SQLs taking more than 5 seconds, when one query retained my attention. I drilled down to its corresponding SQL Monitoring Report and started looking carefully to its execution plan. There was a particular index range scan operation which retained my attention because it was consuming a huge amount of logical I/O (buffers). A careful examination of the index and its underlying table reveals that the latter is range partitioned while the former is a local non prefixed index (a locally partitioned index which doesn’t include the partition key in its definition).

My curiosity is so that I issued a query to see how many non prefixed indexes exist in the whole bunch of partitioned tables this application owns. The query of course returned several rows. When I asked the developer about the reason, he said that this is a ”standard” they have adopted because they are, every couple of months, truncating old partitions; and having local indexes (even non prefixed ones) is a good idea in this case because they don’t have to rebuild any global indexes (by the way there is the UPDATE GLOBAL INDEXES clause for that).

And here where the problem resides: ignoring the technology. Partitioning is a nice feature which could damage the performance when it is wrongly designed. Creating a locally non-prefixed index without knowing the collateral effects they can produce if partition pruning is not guaranteed is something I have very often seen in my daily consultancy work. In order to explain this particular client situation I have engineered the following partitioned table with 1493 partitions (you should open the file in a new page copy the content into a .sql file and execute it). Table to which I have attached a locally non prefixed index (LC_NON_PREFIXED_TYP_I).  Here below are the observations I can emphasize when selecting from this table:

SQL> desc partitioned_tab

Name               Null?     Type
------------------ --------- -------------------------------
MHO_ID             NOT NULL NUMBER(10)
MHO_DATE           NOT NULL DATE       -- partition key
MHO_CODE           NOT NULL VARCHAR2(1 CHAR)
MHO_TYP_ID         NOT NULL NUMBER(10) -- indexed column

The important question here is how would react the database to a query that doesn’t eliminate partitions (because it doesn’t include the partition key in its predicate) and which will be honored via a locally partitioned non prefixed index. Something like this:

select * from partitioned_tab where mho_typ_id = 0;

In the presence of an index of this type:

CREATE INDEX LC_NON_PREFIXED_TYP_I ON partitioned_tab (MHO_TYP_ID) LOCAL;

Here are the results

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                  | Starts | E-Rows | A-Rows |Buffers |Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                       |      1 |        |   1493 |   2984 |      |       |
|   1 |  PARTITION RANGE ALL               |                       |      1 |   1493 |   1493 |   2984 |    1 |  1493 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TAB       |   1493 |   1493 |   1493 |   2984 |    1 |  1493 |
|*  3 |    INDEX RANGE SCAN                | LC_NON_PREFIXED_TYP_I |   1492 |   1493 |   1493 |   1492 |    1 |  1493 |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(MHO_TYP_ID=0)

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

Spot how many times the INDEX RANGE SCAN operation has been started: 1492 times. Compare this number to the number of table partitions (1493) and you will find that in such a kind of situation you will do N-1 INDEX RANGE SCAN operations (where N is the number of partitions). That is an enormous waste of time and energy.

Why 1492 INDEX RANGE SCANs?

It is simply because a locally partitioned index contains multiple segments in contrast to a b-tree index which consists of a single segment.

SQL> select count(1) from dba_segments where segment_name = 'LC_NON_PREFIXED_TYP_I';

COUNT(1)
----------
1492

I am not saying that you don’t have to create a locally non prefixed index. What I am trying to emphasize is that when you decide to do so be sure that your queries will eliminate partitions and will hence prune down to a single index partition as it is shown here below when my query is doing partition pruning

SQL> select * from partitioned_tab
     where mho_typ_id = 0
     and  mho_date = to_date('01122012','ddmmyyyy');

MHO_ID MHO_DATE          M MHO_TYP_ID
---------- ----------------- - ----------
1 20121201 00:00:00 Z          0

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                  | Starts | E-Rows | A-Rows | Buffers | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                       |      1 |        |      1 |       2 |       |       |
|   1 |  PARTITION RANGE SINGLE            |                       |      1 |      1 |      1 |       2 |     2 |     2 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TAB       |      1 |      1 |      1 |       2 |     2 |     2 |
|*  3 |    INDEX RANGE SCAN                | LC_NON_PREFIXED_TYP_I |      1 |      1 |      1 |       1 |     2 |     2 |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(MHO_DATE=TO_DATE(' 2012-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
3 - access(MHO_TYP_ID=0)

Since Oracle has succeeded to eliminate non touched partitions (PARTITION RANGE SINGLE) it has pruned down to a single segment index range scan as shown by the Starts information which equals 1. In addition, the consistent gets (Buffers) has been drastically reduced from 2984 to only 2.

That’s when your query is able to eliminate partitions. However, if you have a particular query that can’t eliminate partitions and that you want to cover via an appropriate index then in this case you have better to not local partition the index. Let’s see this in action

SQL> alter index LC_NON_PREFIXED_TYP_I invisible;

Index altered.

SQL> create index gl_typ_i on partitioned_tab(mho_typ_id);

create index gl_typ_i on partitioned_tab(mho_typ_id)
*
ERROR at line 1:
ORA-01408: such column list already indexed

Damn!!! I can’t do it in 11.2.0.3.0

SQL> drop index LC_NON_PREFIXED_TYP_I;

SQL> create index gl_typ_i on partitioned_tab(mho_typ_id);

SQL> select * from partitioned_tab where mho_typ_id = 0;

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name            | Starts | E-Rows | A-Rows |Buffers | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                 |      1 |        |   1493 |   1496 |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| PARTITIONED_TAB |      1 |   1493 |   1493 |   1496 | ROWID | ROWID |
|*  2 |   INDEX RANGE SCAN                 | GL_TYP_I        |      1 |   1493 |   1493 |      4 |       |       |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(MHO_TYP_ID=0)

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

And spot how many index range scan we did this time? Only one. Because there is only one segment for this type of index

SQL> select count(1) from dba_segments where segment_name = 'GL_TYP_I';

COUNT(1)
----------
1

You can also point out that in contrast to the locally non prefixed index we did 50% less of logical I/O – from 2984 down to 1496.

By the way, why do you think Oracle allows the creation of the non prefixed index LC_NON_PREFIXED_TYP_I when it is declared as non unique and refuse to obey you  when you want to create it as a unique index?

SQL> create unique index lc_non_prefixed_typ_i on partitioned_tab (mho_typ_id) local;

*
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index

Simply because Oracle has already measured the impact this kind of index can have on the insert performance if it has allowed it to exist. In this case a mho_typ_id could go in any of the 1493 partitions. How would Oracle proceed to check if the inserted mho_typ_id value has not been already inserted (or is being inserted) without impeaching others to insert into the whole bunch of the 1492 partitions? Is this scalable and performant? Of course it is not.

Bottom Line: when you create a locally non prefixed index (index that doesn’t include the partition key in its definition) then be sure that queries using this index will eliminate partitions. Otherwise,  the more partitions you have the more index partitions you will range scan and the more logical I/O you will do