Partitioning: one table, many pieces

A partitioned table looks like one table to every query, but is stored as separate pieces, split by a column's value. Queries for one season read only that season's piece ("partition pruning"), and an old season can be archived or dropped in a second instead of deleted row by row.

The test case

Store the deliveries split by season, then count the balls in each season's partition.

Setup

begin
  for t in (select table_name from user_tables where table_name in ('D104_RANGE', 'D104_LIST', 'D104_INTERVAL')) loop
    execute immediate 'drop table ' || t.table_name || ' purge';
  end loop;
end;
/

Expected result

The same answer three ways, and the queries never mention partitions at all except to prove the point with PARTITION FOR (...).

Way 1: range partitions

Setup

create table d104_range
partition by range (season) (
  partition p2025 values less than (2026),
  partition p2026 values less than (2027),
  partition pmax  values less than (maxvalue)
)
as select season, match_id, innings_no, seq_no, runs_batter from cr_balls_v;

select 2025 as season, count(*) as balls from d104_range partition for (2025) union all
select 2026,           count(*)          from d104_range partition for (2026);

Each partition holds values less than its bound. MAXVALUE catches everything after. The usual choice for dates: one partition per month or year.

Way 2: list partitions

Setup

create table d104_list
partition by list (season) (
  partition p2025  values (2025),
  partition p2026  values (2026),
  partition pother values (default)
)
as select season, match_id, innings_no, seq_no, runs_batter from cr_balls_v;

select 2025 as season, count(*) as balls from d104_list partition for (2025) union all
select 2026,           count(*)          from d104_list partition for (2026);

Each partition lists its values. Good for regions, statuses, tenants.

Way 3: interval partitions (they create themselves)

Setup

create table d104_interval
partition by range (season) interval (1) (
  partition p_first values less than (2026)
)
as select season, match_id, innings_no, seq_no, runs_batter from cr_balls_v;

select 2025 as season, count(*) as balls from d104_interval partition for (2025) union all
select 2026,           count(*)          from d104_interval partition for (2026);

Only the first partition is declared; Oracle adds one for each new season the moment a row arrives (with a system name like SYS_P1234). PARTITION FOR (2026) finds it by value, so you never need the name.

Archiving a season

alter table d104_range drop partition for (2025);    -- a second, however many rows
alter table d104_range truncate partition p2026;      -- empty one season, keep the rest

19c and 26ai

The same on both. Partitioning is a separately licensed option of Enterprise Edition on-premises; it's included in Autonomous Database and in Oracle AI Database 26ai Free.

Gotchas

• Partition by what you filter on. If queries rarely mention season, partitioning by it buys nothing for them.
• Don't make thousands of tiny partitions. Partitioning helps big tables; on a table this size it's a demonstration, not an optimization.
• Global indexes on a partitioned table need maintaining when you drop a partition (UPDATE INDEXES), or they become unusable.