Compare a partition-local index with an Oracle-style global index on the same partitioned table: what does each buy you at query time, and what does each cost you when partitions are dropped or exchanged?

## Definitions **Local index** (Oracle `LOCAL`; SQL Server calls the concept an *aligned* index; the only option in PostgreSQL and MySQL): the index is partitioned exactly the way the table is. Partition *i* of the table has its own index containing only partition *i*'s rows. There is a one-to-one correspondence between table partitions and index partitions. **Global index** (Oracle `GLOBAL`; SQL Server *nonaligned*): the index is a structure over the whole table, independent of how the table is partitioned. It may itself be non-partitioned, or partitioned on a *different* key — for example, the table ranged by `order_date` while a global index on `customer_id` is hash-partitioned by `customer_id`. ## Query-time behaviour The question is always: can the engine restrict the search to a few partitions? - Predicate includes the partition key → the planner prunes, and a local index is ideal: a small, shallow tree with high cache residency. - Predicate omits the partition key → a local index means one probe per surviving partition plus an append/merge. With 200 partitions, a 5-microsecond point lookup becomes a millisecond of work, and any `ORDER BY ... LIMIT` may need rows gathered from all partitions before it can answer. A global index answers the same query with a single probe, exactly as if the table were not partitioned. So global indexes exist mainly to preserve OLTP access paths on columns unrelated to the partition key: `SELECT * FROM orders WHERE order_id = ?` on a table partitioned by month. ## Constraint behaviour A unique local index enforces uniqueness only inside each partition; two identical values in different partitions both succeed. Therefore engines with only local indexes require every partition-key column to be part of any `PRIMARY KEY`/`UNIQUE` constraint. A **global unique index** removes that restriction: Oracle can enforce `UNIQUE (order_id)` on a table partitioned by `order_date`, because one structure sees every row. This is frequently the actual reason a team wants global indexes. ## Maintenance-time behaviour — the real cost Partitioning is usually adopted for cheap data lifecycle: `DROP PARTITION` deletes a month of data in milliseconds because it is a metadata operation on a separate physical object. Local indexes preserve that: the partition's index segments are dropped with it. A global index breaks the isolation. Its entries point at rows in the partition you just dropped, so the engine must either: 1. Mark the index **UNUSABLE** (Oracle's default for `DROP`/`TRUNCATE`/`EXCHANGE PARTITION` without `UPDATE INDEXES` / `UPDATE GLOBAL INDEXES`), leaving every query that depended on it to fall back to full scans until you rebuild — a full rebuild of a multi-hundred-GB index; or 2. Maintain it inline (`UPDATE INDEXES`), which turns an O(1) metadata drop into work proportional to the number of rows removed, holding locks and generating redo for the duration. Oracle mitigates this with **asynchronous global index maintenance**: the drop returns quickly and the orphaned entries are cleaned up later by a maintenance job, but the index carries dead entries in the meantime and cleanup still has to run. Other costs of a global index: - **Single hot structure.** All partitions' inserts hit one tree; with monotonically increasing keys, the right-hand leaf becomes a contention point. - **Build/rebuild is all-or-nothing.** You cannot rebuild it a slice at a time or skip cold data; a rebuild is one long, large operation. - **No selective indexing.** You cannot index only the hot partitions. - **Attach/exchange friction.** Swapping a prepared table into the partitioned table is instant with local indexes and requires global-index maintenance otherwise. ## Choosing A workable decision procedure: 1. Pick the partition key from the dominant access pattern *and* the retention requirement. 2. Default all indexes to local. 3. List the access paths that will now be unprunable. Estimate their cost as (partition count × probe cost) at the partition count you'll have in two years — not today's count. 4. If one of those paths is latency-critical or a uniqueness rule cannot include the partition key, and your engine supports it, add a *global* index for exactly that path and change your partition-lifecycle scripts to maintain it (`UPDATE INDEXES`, or rebuild windows). 5. If your engine has no global indexes, the alternatives are: repartition on the key that matters, keep a small non-partitioned lookup/registry table mapping the alternate key to the partition key, or accept the fan-out. ## One-line summary Local indexes make partitions independent; global indexes make the table look unpartitioned again. You pay for whichever property you didn't choose.