How to Partition Tables by Range in Oracle

Large tables of events, such as flights, bookings, or log entries, grow by date and are usually queried by date. Range partitioning stores each period, say each month, in its own partition. Queries for one month read only that partition, old months can be dropped or archived in a moment, and interval partitioning creates new partitions automatically as data arrives.

Code for This Guide

The main examples are in the examples/special-tables and examples/flashback-sampling folders of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

create table table (...)
partition by range (column) [interval (expr)]
(partition name values less than (value) [, ...])

Each partition holds the rows whose key is below its bound and at or above the previous partition's bound. With INTERVAL, Oracle creates a new partition of that size whenever a row arrives beyond the last bound, naming it SYS_P followed by a number.

Monthly Interval Partitions

This table starts with one partition for everything before 2026, and an interval of one month.

Example:

create table flight_log (
  flight_id  number,
  flown_on   date,
  status     varchar2(10)
)
partition by range (flown_on) interval (numtoyminterval(1, 'MONTH'))
(partition p_before_2026 values less than (date '2026-01-01'));

insert into flight_log
select flight_id, cast(sys_extract_utc(scheduled_departure) as date), status from flights;
commit;
exec dbms_stats.gather_table_stats(user, 'FLIGHT_LOG')

select partition_name, interval, num_rows
from   user_tab_partitions
where  table_name = 'FLIGHT_LOG'
order  by partition_position;

Output:

Table FLIGHT_LOG created.

2,856 rows inserted.

Commit complete.

PL/SQL procedure successfully completed.

PARTITION_NAME    INTERVAL       NUM_ROWS
_________________ ___________ ___________
P_BEFORE_2026     NO                    5
SYS_P1638         YES                 981
SYS_P1639         YES                 888
SYS_P1640         YES                 981
SYS_P1641         YES                   1

Loading the flights created four monthly partitions automatically, shown with INTERVAL = YES, and the row counts per partition come from the statistics.

Query One Partition

Usually a WHERE condition on the key is enough: the optimizer reads only the partitions it needs, which is called partition pruning. A query can also name a partition, or the partition that holds a given key value.

Example:

create table flights_by_month
partition by range (dep_date) (
  partition p_2026_01 values less than (date '2026-02-01'),
  partition p_2026_02 values less than (date '2026-03-01'),
  partition p_2026_03 values less than (date '2026-04-01'),
  partition p_later   values less than (maxvalue)
)
as select flight_id, flight_no,
          cast(sys_extract_utc(scheduled_departure) as date) as dep_date
   from   flights;

select count(*) as feb_flights from flights_by_month partition (p_2026_02);

select count(*) as march_flights
from   flights_by_month partition for (date '2026-03-15');

Output:

Table FLIGHTS_BY_MONTH created.

   FEB_FLIGHTS
______________
           888

   MARCH_FLIGHTS
________________
             981

PARTITION FOR (date '2026-03-15') finds the partition for that date without knowing its system-generated name.

Things to Know

• Choose the key that queries filter on most, usually a date.
• Indexes on partitioned tables can be local, partitioned like the table, or global.
• Oracle AI Database 26ai Free includes partitioning.

Related Guides

• How to Create Index-Organized Tables in Oracle

Conclusion

Range partitioning stores each range of a key, typically a period of time, in its own partition, and INTERVAL adds partitions automatically. Queries that filter on the key read only the partitions they need, and each partition can be maintained on its own.