Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Oracle global vs. local partitioned indexes

Oracle Database Tips by Donald BurlesonJune 4, 2015

Question: I have a partitioned table with a global partitioned index and I suspect that the global index is causing me poor SQL performance.  Is there a performance boost in moving the partition index from a global index to a local partitioned index?  If so, what is the nature of the SQL performance improvement?

Answer:  Let's start with a few major areas of partitioned index performance:

  • Unique vs non-unique:  The docs note that global partitioned indexes and unique, local partitioned indexes provide better performance than nonunique local indexes because they minimize the number of index partition probes. 
  • DML vs. SQL:  We need to understand that the primary benefit of local indexes is manageability and improved DML performance, and not necessarily SQL performance.  In many ways, the ability to locally partition Oracle indexes has more performance potential than table partitioning, since index contention is a big issue as the base tables are updated.
  • Parallelism:  For index fast full scans (Index FFS) and multi-partition range scans, Oracle parallel query will automatically fire-off additional processes to read each local index partition separately.  But beware, for SQL that only probes for individual rows, parallelism has no effect.
  • Partition pruning:  In some cases of full index scans, local indexes can be isolated to reduce the total I/O required to satisfy the SQL request.

Index partitioning with Oracle

A local partitioned index creates a one-for-one match between the indexes and the partitions in the table. Of course, the key value for the table partition and the value for the local index must be identical. The second method is called GLOBAL and allows the index to have any number of partitions.

The partitioning of the indexes is transparent to all SQL queries. The great benefit is that the Oracle query engine will scan only the index partition that is required to service the query, thus speeding up the query significantly. In addition, the Oracle parallel query engine will sense that the index is partitioned and will fire simultaneous queries to scan the indexes.

Local partitioned indexes

Local partitioned indexes allow us to take individual partitions of a table and indexes offline for maintenance (or reorganization) without affecting the other partitions and indexes in the table.

In a local partitioned index, the key values and number of index partitions will match the number of partitions in the base table.

on all_fact (order_date)
(PARTITION name_idx1),
(PARTITION name_idx2),
(PARTITION name_idx3);

Oracle will automatically use equal partitioning of the index based upon the number of partitions in the indexed table. For example, in the above definition, if we created four indexes on all_fact, the CREATE INDEX would fail since the partitions do not match. This equal partition also makes index maintenance easier, since a single partition can be taken offline and the index rebuilt without affecting the other partitions in the table.

Global partitioned indexes

A global partitioned index is used for all other indexes except for the one that is used as the table partition key. Global indexes partition OLTP (online transaction processing) applications where fewer index probes are required than with local partitioned indexes. In the global index partition scheme, the index is harder to maintain since the index may span partitions in the base table.

For example, when a table partition is dropped as part of a reorganization, the entire global index will be affected. When defining a global partitioned index, the DBA has complete freedom to specify as many partitions for the index as desired.

Now that we understand the concept, let's examine the Oracle CREATE INDEX syntax for a globally partitioned index:

on all_fact (item_nbr)

Here, we see that the item index has been defined with five partitions, each containing a subset of the index range values. Note that it is irrelevant that the base table is in three partitions. In fact, it is acceptable to create a global partitioned index on a table that does not have any partitioning.


Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.



Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster