Mohamed Houri’s Oracle Notes

November 7, 2013

Index design I : Partitioned Index: Global or local?

Filed under: Oracle — hourim @ 2:28 pm

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


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)

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.


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';


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');

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


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)

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';


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


  1. very clear


    Comment by Polet Frank — November 8, 2013 @ 7:14 am | Reply

  2. Frank

    Thanks for your comment.

    Comment by hourim — November 8, 2013 @ 8:03 am | Reply

  3. Good observation!!

    Thanks for sharing..



    Comment by Yousuf — November 9, 2013 @ 9:00 am | Reply

  4. Hi Mohammed,

    a few comments:

    1) it doesn’t really make sense to make the distinction between prefixed and non-prefixed local indexes for modern Oracle versions. Local indexes work well when there is partition pruning. This is true regardless to whether or not the local index starts with the partitioning key. It’s true when the local index doesn’t contain the partitioning key at all. See comments by Jonathan Lewis in the thread below:

    2) the developer you spoke with has made a very good point — local indexes provide perfect maintainability because you can do DDL operations on partition level. Sure, you can do DDL with global indexes as well, and you can even achieve this with one statement using UPDATE GLOBAL INDEXES clause. The point you seem to be missng is that this would be prohibitively slow if the table is big enough. Imagine this: you have a 10 Tb table on a standard Oracle server (i.e. not Exadata), you need to periodically archive (truncate, dropetc.) partitions your maintenance window is 12 hours. Will that work with global indexes? Most probably not. 12c is offering asynchronous update of global indexes, and not everybody is on 12c yet (and it’s not a perfect solution for those who is, because your ability to perform queries will still be affected during the asynchronous rebuild).

    3) what you wrote about local indexes resulting in poor performance in absence of partition pruning is of course true. However, the reality is that performance and manageability often are conflicting requirements, and you may face situations when you have to sacrifice performance of some of your queries to make your data manageable. There is a very good article which touches upon this subject by Martin Widlake ( ), and I myself am planning to explore this subject in more detail in near future.

    Best regards,

    Comment by savvinov — November 12, 2013 @ 2:19 pm | Reply

  5. Hi Nicolay,

    Thanks a lot for your comment. I like very much your comment numbered 3. And by the way, you are doing a good work on otn.

    Best regards

    Comment by hourim — November 12, 2013 @ 2:43 pm | Reply

  6. […] Global or Local Partitioned Index […]

    Pingback by Index design | Mohamed Houri’s Oracle Notes — September 3, 2014 @ 2:56 pm | Reply

  7. […] eliminating partitions (partition range all). This trouble shooting issue paved the way to the partitioned index: global or local article via which the designer of the “culprit” index has perfectly learnt the […]

    Pingback by Partition by virtual column | Mohamed Houri’s Oracle Notes — October 1, 2014 @ 3:19 pm | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog


Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: