Partitioning

Interval partitioning extends range partitioning with automatic partition creation — the database creates new partitions as data arrives outside the defined range. Reference partitioning allows a child table to inherit the partitioning of its parent via a foreign key, ensuring rows in related tables are always co-located in the same partition. Interval Partitioning How Automatic Partition Creation Works An interval-partitioned table defines a seed partition (at minimum one VALUES LESS THAN partition) and an interval clause. When an INSERT or MERGE places a row in a value range that has no existing partition, Oracle automatically creates the partition. ...

Partition pruning is the optimizer’s ability to eliminate partitions from a query’s access path based on predicate analysis. When it works, a query against a 10 TB table might access only 50 GB of relevant partitions. When it fails, you get a full table scan across every partition — often with no visible indication in the execution plan that pruning is not occurring. How Partition Pruning Works The optimizer evaluates predicates in the WHERE clause against the partition key definition during the parse phase. If a predicate can be resolved to a specific partition range, those partitions are excluded from the access path. ...