How Reference Partition Boosts Oracle Query Performance and Simplifies Management

Performance Perspective

The first query without any partition predicate triggers a Partition List All operation, causing full scans of both master and detail tables and a high cost (Cost 642). Adding an owner='SCOTT' predicate restricts the master table to a single partition, resulting in a Partition List Single operation and a reduced cost (Cost 495).

Plan hash value: 2684484261
| Id | Operation               | Name    | Rows  | Bytes | Cost (%CPU) |
| 0  | SELECT STATEMENT         |         | 238K  | 18M   | 642 (3)      |
| 1  | PARTITION LIST ALL       |         | 238K  | 18M   | 642 (3)      |
| 2  | MERGE JOIN               |         | 238K  | 18M   | 642 (3)      |
| 3  | SORT JOIN                |         | 120K  | 4936K | 222 (3)      |
| 4  | TABLE ACCESS FULL        | T_MASTER| 120K  | 4936K | 217 (1)      |
|*5  | SORT JOIN                |         | 240K  | 9403K | 419 (3)      |
| 6  | TABLE ACCESS FULL        | T_DETAIL| 240K  | 9403K | 412 (1)      |

When the owner='SCOTT' condition is applied, the plan changes to:

Plan hash value: 3648887064
| Id | Operation               | Name    | Rows | Bytes | Cost (%CPU) |
| 0  | SELECT STATEMENT        |         | 2600 | 198K  | 495 (1)     |
| 1  | PARTITION LIST SINGLE   |         | 2600 | 198K  | 495 (1)     |
|*2  | HASH JOIN               |         | 2600 | 198K  | 495 (1)     |
|*3  | TABLE ACCESS FULL       | T_MASTER| 1312 | 49856 | 82 (0)      |
| 4  | TABLE ACCESS FULL       | T_DETAIL| 240K | 9403K | 412 (1)     |

Creating a non‑partitioned copy of the detail table ( t_normal) and running the same query shows a full table scan on the child table, confirming that the benefit disappears without a matching partition.

Plan hash value: 1706510341
| Id | Operation               | Name    | Rows | Bytes | Cost (%CPU) |
| 0  | SELECT STATEMENT        |         | 2600 | 198K  | 494 (1)     |
|*1  | HASH JOIN               |         | 2600 | 198K  | 494 (1)     |
| 2  | PARTITION LIST SINGLE   |         | 1312 | 49856 | 82 (0)      |
|*3  | TABLE ACCESS FULL       | T_MASTER| 1312 | 49856 | 82 (0)      |
| 4  | TABLE ACCESS FULL       | T_NORMAL| 240K | 9403K | 411 (1)     |

Adding a local index on the foreign‑key column ( idx_t_detail_mas) does not change the plan; the optimizer still prefers the partition‑based cost.

Collecting detailed column statistics (histograms) and re‑optimising the query yields a plan that uses an INDEX RANGE SCAN on the new index, dropping the total cost to 136.

Plan hash value: 2296204501
| Id | Operation                         | Name            | Rows | Cost (%CPU) |
| 0  | SELECT STATEMENT                  |                 | 35   | 136 (0)     |
| 1  | PARTITION LIST SINGLE             |                 | 35   | 136 (0)     |
| 2  | NESTED LOOPS                      |                 | 35   | 136 (0)     |
| 3  | NESTED LOOPS                      |                 | 36   | 136 (0)     |
|*4  | TABLE ACCESS FULL                 | T_MASTER        | 18   | 82 (0)      |
|*5  | INDEX RANGE SCAN                  | IDX_T_DETAIL_MAS| 2    | 1  (0)      |
| 6  | TABLE ACCESS BY LOCAL INDEX ROWID | T_DETAIL        | 2    | 3  (0)      |

Management Perspective

Reference Partition tightly binds master and child partitions. Dropping partition p1 from t_master automatically removes the corresponding child partition, as shown by the successful ALTER TABLE t_master DROP PARTITION p1 command.

Querying DBA_TAB_PARTITIONS after the drop confirms the synchronized removal of partitions from both tables.

TABLE_NAME PARTITION_ HIGH_VALUE   NUM_ROWS
T_DETAIL   P0                     67992
T_DETAIL   P3                     97096
T_MASTER   P0        'PUBLIC'      33996
T_MASTER   P3        default        48548

Conclusion

Reference Partition, introduced in Oracle 11g, offers tangible performance improvements for foreign‑key joins and simplifies partition management by ensuring master‑child partition alignment, though its benefits can be limited when index costs outweigh partition savings.