Range, List, Hash, Interval, Composite Partitioning, and Partitioning-Key Design

Learning outcomes

ServiceHub's work-order history has grown from millions to hundreds of millions of rows. Most operational queries ask for “this week” or “this region,” while compliance deletes entire old months. A single heap table still works logically, but maintenance and scan scope become increasingly expensive. Partitioning splits one logical table/index into physical pieces that Oracle can prune, maintain and store independently. It does not create independent databases or application shards.

1. One table, one transaction model, many physical partitions

A partitioned table remains one schema object for SQL, constraints, privileges and transactions. The optimizer can eliminate irrelevant partitions (partition pruning), while administrators can move, exchange, truncate or drop physical partitions as lifecycle units. Oracle can also partition indexes locally or globally.

2. Deliberately wrong: a finite range table with no future partition

BEGIN  EXECUTE IMMEDIATE 'DROP TABLE servicehub_events_bad PURGE';EXCEPTION WHEN OTHERS THEN  IF SQLCODE != -942 THEN RAISE; END IF;END;/CREATE TABLE servicehub_events_bad (  event_id      NUMBER PRIMARY KEY,  event_ts      DATE NOT NULL,  region_code   VARCHAR2(8) NOT NULL,  event_type    VARCHAR2(20) NOT NULL)PARTITION BY RANGE (event_ts) (  PARTITION p_before_jul VALUES LESS THAN (DATE '2026-07-01'),  PARTITION p_q3         VALUES LESS THAN (DATE '2026-10-01'));INSERT INTO servicehub_events_badVALUES (1, DATE '2026-10-15', 'AZ-N', 'ASSIGNED');-- Expected:-- ORA-14400: inserted partition key does not map to any partition

The insert fails because the partition map itself has no legal home for 15 October. Adding application retry logic does not repair the physical design.

3. Interval partitioning repairs the future-boundary problem

DROP TABLE servicehub_events_bad PURGE;CREATE TABLE servicehub_events_p (  event_id      NUMBER NOT NULL,  event_ts      DATE NOT NULL,  region_code   VARCHAR2(8) NOT NULL,  event_type    VARCHAR2(20) NOT NULL,  payload       VARCHAR2(200),  CONSTRAINT sh19_events_pk PRIMARY KEY (event_id,event_ts))PARTITION BY RANGE (event_ts)INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) (  PARTITION p_before_jul_2026    VALUES LESS THAN (DATE '2026-07-01'));INSERT INTO servicehub_events_p VALUES (1001, DATE '2026-07-05', 'AZ-N','OPEN',RPAD('a',50,'a'));INSERT INTO servicehub_events_p VALUES (1002, DATE '2026-08-10', 'AZ-S','ASSIGNED',RPAD('b',50,'b'));INSERT INTO servicehub_events_p VALUES (1003, DATE '2026-10-15', 'AZ-N','CLOSED',RPAD('c',50,'c'));COMMIT;

Oracle materializes interval partitions as needed after the transition partition. This prevents the “forgot next month” failure, but it does not decide how long data should remain, where it should live, or whether each month's volume is balanced.

4. Verify the real physical routing

BEGIN  DBMS_STATS.GATHER_TABLE_STATS(    ownname => USER,    tabname => 'SERVICEHUB_EVENTS_P'  );END;/SELECT  partition_name,  partition_position,  interval,  num_rows,  blocksFROM user_tab_partitionsWHERE table_name='SERVICEHUB_EVENTS_P'ORDER BY partition_position;SELECT  partitioning_type,  interval,  partition_countFROM user_part_tablesWHERE table_name='SERVICEHUB_EVENTS_P';

Dictionary row counts depend on statistics collection, not immediate DML. The partition list proves physical objects exist; it does not prove the optimizer prunes them for a particular predicate—that is Lesson 2.

5. Range/list/hash/composite are different answers to different questions

CREATE TABLE servicehub_region_rules (  region_code VARCHAR2(8) NOT NULL,  rule_name   VARCHAR2(80) NOT NULL)PARTITION BY LIST (region_code) (  PARTITION p_az_n VALUES ('AZ-N'),  PARTITION p_az_s VALUES ('AZ-S'),  PARTITION p_other VALUES (DEFAULT));

CREATE TABLE servicehub_event_hash (  event_id NUMBER NOT NULL,  region_id NUMBER NOT NULL,  payload VARCHAR2(100))PARTITION BY HASH (region_id)PARTITIONS 8;

CREATE TABLE servicehub_event_composite (  event_id NUMBER NOT NULL,  event_ts DATE NOT NULL,  region_id NUMBER NOT NULL,  payload VARCHAR2(100))PARTITION BY RANGE (event_ts)SUBPARTITION BY HASH (region_id)SUBPARTITIONS 4 (  PARTITION p_2026_q3 VALUES LESS THAN (DATE '2026-10-01'),  PARTITION p_2026_q4 VALUES LESS THAN (DATE '2027-01-01'));

The examples are intentionally small. A real subpartition count must come from concurrency, maintenance, segment size and storage/object-count evidence—not a generic power-of-two rule.

6. Partitioning key design is the real architecture decision

A good key simultaneously helps important predicates prune, aligns with retention/load windows and avoids pathological skew. Partitioning WORK_ORDERS by a surrogate ID may distribute rows but make “drop everything older than seven years” expensive. Partitioning by time may align lifecycle perfectly but not isolate a single high-volume tenant. Composite range-by-time plus hash/list subpartitioning can address both at greater complexity.

7. Licensing and scope

The current 26ai matrix includes Oracle Partitioning in Free. It is unavailable in SE2-ODA/BaseDB SE/BaseDB EE, is an extra-cost option on EE/EE-ES, and is included in BaseDB EE-HP, BaseDB EE-EP and ExaDB. Production entitlement must be checked for the exact offering even when the SQL syntax is familiar.

Partitioning DDL is schema/PDB-scoped. The owner needs normal object-creation privileges and quota; no instance restart is required. Existing large-table conversion may require online redefinition, exchange/load strategy or a maintenance window depending on availability needs.

8. Cleanup

DROP TABLE servicehub_events_p PURGE;DROP TABLE servicehub_region_rules PURGE;DROP TABLE servicehub_event_hash PURGE;DROP TABLE servicehub_event_composite PURGE;

9. Production judgment

Partition when access and lifecycle behavior justify independent physical units: pruning, rolling-window retention, load exchange, tablespace placement or partition-wise operations. Do not partition every large table merely because it is large; every extra partition adds metadata, statistics, index and operational work.

No Chapter 19 lab changes COMPATIBLE, PDB topology or initialization parameters. Lesson 2 now proves whether the optimizer actually eliminates partitions and shows how local/global indexes react when those physical units are moved or removed.

Authoritative references

• Partitioning Concepts — range/list/hash/interval/composite concepts
• Partition Administration — partition DDL and lifecycle
• CREATE TABLE — partition clauses
• ORA-14400 — unmapped partition-key failure
• Licensing Information — Partitioning offering matrix