On a partitioned table, an index can follow the table's partitions or ignore them. A local index has one index partition per table partition, so it is maintained along with the table's partitions. A global index is one structure over the whole table. The choice decides what happens when you drop, truncate, or exchange a partition.
Code for This Guide
The main examples are in the examples/indexes folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
create index index on table (column [, ...]) local create index index on table (column [, ...]) [global] alter table table drop partition p [update indexes]
A Local and a Global Index
FLIGHT_LOG is interval-partitioned by month. The status index is local and the flight ID index global.
Example:
create index flight_log_status_ix on flight_log (status) local; create index flight_log_id_ix on flight_log (flight_id) global; select index_name, partitioned, locality, partition_count from user_indexes left join user_part_indexes using (index_name, table_name) where table_name = 'FLIGHT_LOG';
Output:
Index FLIGHT_LOG_STATUS_IX created. Index FLIGHT_LOG_ID_IX created. INDEX_NAME PARTITIONED LOCALITY PARTITION_COUNT _______________________ ______________ ___________ __________________ FLIGHT_LOG_STATUS_IX YES LOCAL 1048575 FLIGHT_LOG_ID_IX NO
The local index is partitioned. For an interval-partitioned table, its partition count shows the maximum number of partitions, 1,048,575, rather than the number that exist. The global index is not partitioned.
Dropping a Partition
Here a table with three range partitions has a local and a global index. Dropping a partition takes the local index partition with it, but leaves the global index unusable, until it is rebuilt or the drop maintains it with UPDATE INDEXES.
Example:
-- dropping a partition: local index partitions go with it, the global index becomes unusable alter table flight_log2 drop partition p_2025; select index_name, status from user_indexes where table_name = 'FLIGHT_LOG2' order by index_name; select index_name, count(*) as index_partitions from user_ind_partitions where index_name = 'FLIGHT_LOG2_STATUS_IX' group by index_name; -- with UPDATE INDEXES, the global index stays usable alter index flight_log2_id_ix rebuild; alter table flight_log2 drop partition p_jan update indexes; select index_name, status from user_indexes where table_name = 'FLIGHT_LOG2' order by index_name;
Output:
Table FLIGHT_LOG2 altered. INDEX_NAME STATUS ________________________ ___________ FLIGHT_LOG2_ID_IX UNUSABLE FLIGHT_LOG2_STATUS_IX N/A INDEX_NAME INDEX_PARTITIONS ________________________ ___________________ FLIGHT_LOG2_STATUS_IX 2 Index FLIGHT_LOG2_ID_IX altered. Table FLIGHT_LOG2 altered. INDEX_NAME STATUS ________________________ _________ FLIGHT_LOG2_ID_IX VALID FLIGHT_LOG2_STATUS_IX N/A
After the first drop, the global index is UNUSABLE and the local index has two partitions left. After rebuilding it, a drop with UPDATE INDEXES keeps it VALID. The local index's status is N/A because its status is kept per partition.
Local or Global
| Index | Use it for |
|---|---|
| Local | Queries that filter on the partition key as well; tables whose partitions are dropped or exchanged often |
| Global | Unique lookups on a column that is not the partition key, such as an ID across all months |
Related Guides
Conclusion
Local indexes are partitioned like their table and follow its partition maintenance; global indexes span all partitions and become unusable after partition operations unless UPDATE INDEXES maintains them. Choose local for partition-aligned access and global for lookups across partitions.
See my articles more often when you search Google.
