Partition Pruning, Partition-Wise Joins, Local/Global Indexes, and Maintenance Operations

Learning outcomes

ServiceHub partitioned its event history by quarter, but a dashboard filtering one week still scans all partitions. Later, a DBA moves one partition and unexpectedly leaves indexes unusable. Partitioning helps only when predicates let the optimizer prune partitions and maintenance runbooks understand the index topology.

1. Build a quarterly table with local and global indexes

BEGIN  EXECUTE IMMEDIATE 'DROP TABLE servicehub_orders_p PURGE';EXCEPTION WHEN OTHERS THEN  IF SQLCODE != -942 THEN RAISE; END IF;END;/CREATE TABLE servicehub_orders_p (  order_id       NUMBER NOT NULL,  created_at     DATE NOT NULL,  technician_id  NUMBER NOT NULL,  status_code    VARCHAR2(12) NOT NULL,  amount         NUMBER(10,2) NOT NULL,  CONSTRAINT sh19_orders_pk PRIMARY KEY(order_id,created_at))PARTITION BY RANGE(created_at) (  PARTITION p2026q1 VALUES LESS THAN (DATE '2026-04-01'),  PARTITION p2026q2 VALUES LESS THAN (DATE '2026-07-01'),  PARTITION p2026q3 VALUES LESS THAN (DATE '2026-10-01'),  PARTITION p2026q4 VALUES LESS THAN (DATE '2027-01-01'),  PARTITION p_future VALUES LESS THAN (MAXVALUE));CREATE INDEX sh19_status_lixON servicehub_orders_p(status_code,created_at)LOCAL;CREATE INDEX sh19_tech_gixON servicehub_orders_p(technician_id);INSERT INTO servicehub_orders_pSELECT  LEVEL,  DATE '2026-01-01' + MOD(LEVEL,365),  100 + MOD(LEVEL,17),  CASE MOD(LEVEL,4)    WHEN 0 THEN 'OPEN'    WHEN 1 THEN 'CLOSED'    WHEN 2 THEN 'HOLD'    ELSE 'ASSIGNED'  END,  MOD(LEVEL*17,10000)/10FROM dualCONNECT BY LEVEL <= 30000;COMMIT;BEGIN  DBMS_STATS.GATHER_TABLE_STATS(USER,'SERVICEHUB_ORDERS_P',cascade=>TRUE);END;/

2. Prove pruning with runtime plan evidence

SELECT /*+ gather_plan_statistics */       COUNT(*),SUM(amount)FROM servicehub_orders_pWHERE created_at >= DATE '2026-08-01'  AND created_at <  DATE '2026-08-08';SELECT *FROM TABLE(  DBMS_XPLAN.DISPLAY_CURSOR(    NULL,NULL,'ALLSTATS LAST +PARTITION +PREDICATE'  ));

Look for partition iterator/access operations and the Pstart/Pstop columns. With literal boundary values Oracle can often identify the exact partition range at parse time (static pruning). With bind values, joins or runtime-derived values, the plan can show KEY-style markers indicating dynamic pruning.

3. A nonpartition-key predicate cannot magically prune time partitions

SELECT /*+ gather_plan_statistics */       COUNT(*)FROM servicehub_orders_pWHERE technician_id=107;SELECT *FROM TABLE(  DBMS_XPLAN.DISPLAY_CURSOR(    NULL,NULL,'ALLSTATS LAST +PARTITION +PREDICATE'  ));

The global technician index can provide selective access without time pruning. Partitioning and indexing solve different dimensions of access. Adding the time predicate may allow both partition pruning and an appropriate local/global index strategy.

4. Local indexes align partitions; global indexes ignore table-partition boundaries

SELECT index_name,partitioned,statusFROM user_indexesWHERE table_name='SERVICEHUB_ORDERS_P'ORDER BY index_name;SELECT index_name,partition_name,statusFROM user_ind_partitionsWHERE index_name='SH19_STATUS_LIX'ORDER BY partition_position;

5. Deliberately wrong: MOVE PARTITION without maintaining indexes

ALTER TABLE servicehub_orders_p  MOVE PARTITION p2026q3;SELECT index_name,statusFROM user_indexesWHERE table_name='SERVICEHUB_ORDERS_P'ORDER BY index_name;SELECT index_name,partition_name,statusFROM user_ind_partitionsWHERE index_name='SH19_STATUS_LIX'  AND partition_name='P2026Q3';

For a heap table, moving a partition without UPDATE INDEXES can mark the matching local index partition and global indexes unusable. With SKIP_UNUSABLE_INDEXES=TRUE, queries may silently choose another plan rather than raise an error, so application success does not prove index health.

ALTER INDEX sh19_status_lix  REBUILD PARTITION p2026q3;ALTER INDEX sh19_tech_gix  REBUILD;SELECT index_name,statusFROM user_indexesWHERE table_name='SERVICEHUB_ORDERS_P'ORDER BY index_name;

6. UPDATE INDEXES trades DDL duration/resources for index availability

ALTER TABLE servicehub_orders_p  DROP PARTITION p2026q1  UPDATE INDEXES;SELECT index_name,status,orphaned_entriesFROM user_indexesWHERE table_name='SERVICEHUB_ORDERS_P'ORDER BY index_name;

For DROP/TRUNCATE PARTITION ... UPDATE INDEXES, modern asynchronous global index maintenance can make the initial global-index work metadata-only while leaving the index usable and cleaning orphan entries later. For other PMOPs, maintaining indexes can add significant redo/undo/runtime.

7. Split, merge, truncate and drop have different data semantics

-- Split a future catch-all at a known boundary:ALTER TABLE servicehub_orders_p  SPLIT PARTITION p_future  AT (DATE '2027-04-01')  INTO (    PARTITION p2027q1,    PARTITION p_future  )  UPDATE INDEXES;-- Merge neighboring ranges:ALTER TABLE servicehub_orders_p  MERGE PARTITIONS p2026q2,p2026q3  INTO PARTITION p2026h2  UPDATE INDEXES;-- Remove rows but retain the partition object:ALTER TABLE servicehub_orders_p  TRUNCATE PARTITION p2026h2  UPDATE INDEXES;

Run these only when the table state matches the named partitions. TRUNCATE/DROP are destructive DDL and do not provide row-by-row rollback. Partition drops are not recovered through the table recycle bin.

8. Partition-wise joins reduce redistribution when partitioning aligns

A full partition-wise join is possible when both joined tables are equipartitioned on the join keys; matching partition pairs can be joined independently. A partial partition-wise join can partition one side dynamically around the partitioned side. These designs reduce memory/data movement for large joins, especially under parallel execution, but the optimizer must still decide the join is beneficial.

CREATE TABLE servicehub_pw_a (  region_id NUMBER NOT NULL,  item_id NUMBER NOT NULL,  metric NUMBER)PARTITION BY HASH(region_id) PARTITIONS 4;CREATE TABLE servicehub_pw_b (  region_id NUMBER NOT NULL,  item_id NUMBER NOT NULL,  label VARCHAR2(40))PARTITION BY HASH(region_id) PARTITIONS 4;-- Join key includes the partitioning key:SELECT COUNT(*)FROM servicehub_pw_a aJOIN servicehub_pw_b b  ON b.region_id=a.region_id AND b.item_id=a.item_id;

Use runtime plans to verify partition iterators/join execution. Do not claim a partition-wise join solely from matching DDL.

9. Cleanup

DROP TABLE servicehub_orders_p PURGE;DROP TABLE servicehub_pw_a PURGE;DROP TABLE servicehub_pw_b PURGE;

10. Production judgment

Design predicates and indexes together with partition boundaries. Local indexes maximize lifecycle independence; global indexes can be justified for selective cross-partition lookups, but PMOP runbooks must preserve/rebuild them intentionally. Validate pruning from runtime plans—not schema diagrams.

Partitioning remains Free-compatible here; no parallel execution or management pack is required. Lesson 3 uses the strongest lifecycle property of partitions: exchanging a prepared nonpartitioned segment into/out of a rolling window without moving every row.

Authoritative references

• Partition Pruning — static/dynamic pruning and Pstart/Pstop
• Partition-Wise Joins — full and partial partition-wise joins
• Partitioning Concepts — local/global indexes
• Partition Administration — UPDATE INDEXES and PMOP effects
• DBMS_XPLAN — runtime execution-plan display