What Is a Local Partitioned Indexes in Oracle Database?
Each local index partition corresponds to a table partition, aligning index maintenance and access with partition lifecycle.
How the Local Partitioned Indexes Algorithm Works
Each local index partition corresponds to a table partition, aligning index maintenance and access with partition lifecycle.
When to Use Local Partitioned Indexes
Partitioned tables where partition pruning and independent partition maintenance are important.
How to Create a Oracle Database Local Partitioned Indexes
Review data quality, existing structures, feature support, build time, and deployment requirements before creating an index on a production table.
CREATE INDEX sales_day_local_ix ON sales (sale_date, customer_id) LOCAL;Local Partitioned Indexes Query Example
This example demonstrates the matching query shape. Check the execution plan to confirm that the engine can use the index.
SELECT customer_id FROM sales WHERE sale_date >= DATE '2026-03-01' AND sale_date < DATE '2026-04-01';Queries Supported by Local Partitioned Indexes
Partitioned tables where partition pruning and independent partition maintenance are important. A similar-looking predicate may not use the same access path; operators, expressions, key order, and data type all matter.
Read Performance, Storage, and Write Costs
Global lookups may touch multiple partitions; some uniqueness rules need partition keys included. Indexes reduce some search work but require storage and add maintenance to writes.
Local Partitioned Indexes Limitations and Common Mistakes
Uniqueness and usability depend on table partition keys and the index definition.
Do not assume that an index being present means the optimizer chose it or that the query became selective.
How to Measure and Tune Local Partitioned Indexes
Design key and partitioning together; test pruning, partition maintenance, and global queries.
Compare representative plans and timings before and after, including concurrent writes and realistic data volumes.
Local Partitioned Indexes Questions
Will the database always use this index?
No. The optimizer weighs selectivity, estimated costs, statistics, cache state, and the amount of data returned.
What should I verify before production?
Confirm version support, query compatibility, write overhead, build/rollback procedures, and index behavior during backup and recovery.
Official Oracle Database Index Documentation
Check Oracle Database Concepts: Indexes and Index-Organized Tables for release-specific requirements and supported operators.