Partition by Expression

 Oracle 26.1 now allows using an Expression in the Partitioning Clause.  In earlier releases, you could do this with a Virtual Column defined in the table.  However, 26.1 does not require the Virtual Column if it is used simply for the Partitioning Clause.

Here is a demo in 19.12 comparing the two methods :

-- using a Virtual Column "ORDER_MONTH"

SQL> l
  1  CREATE TABLE ORDERS_2026_old (
  2      order_id      NUMBER NOT NULL,
  3      order_date    DATE NOT NULL,
  4      customer_id   NUMBER,
  5      order_month   NUMBER GENERATED ALWAYS AS (TO_NUMBER(TO_CHAR(order_date, 'MM'))) VIRTUAL
  6  )
  7  PARTITION BY RANGE (order_month)
  8  (
  9      PARTITION orders_q1 VALUES LESS THAN (4),  -- Months 1, 2, 3
 10      PARTITION orders_q2 VALUES LESS THAN (7),  -- Months 4, 5, 6
 11      PARTITION orders_q3 VALUES LESS THAN (10), -- Months 7, 8, 9
 12      PARTITION orders_q4 VALUES LESS THAN (13)  -- Months 10, 11, 12
 13* )
SQL> /

Table created.

SQL>

-- try the Expression in the Partitioning Clause

SQL> l
  1  CREATE TABLE ORDERS_2026_new (
  2      order_id      NUMBER NOT NULL,
  3      order_date    DATE NOT NULL,
  4      customer_id   NUMBER
  5  )
  6  PARTITION BY RANGE ((TO_NUMBER(TO_CHAR(order_date, 'MM'))))
  7  (
  8      PARTITION orders_q1 VALUES LESS THAN (4),  -- Months 1, 2, 3
  9      PARTITION orders_q2 VALUES LESS THAN (7),  -- Months 4, 5, 6
 10      PARTITION orders_q3 VALUES LESS THAN (10), -- Months 7, 8, 9
 11      PARTITION orders_q4 VALUES LESS THAN (13)  -- Months 10, 11, 12
 12* )
SQL> /
PARTITION BY RANGE ((TO_NUMBER(TO_CHAR(order_date, 'MM'))))
                    *
ERROR at line 6:
ORA-00904: : invalid identifier


SQL>

And now in 26.1 :

-- using the Virtual Column "ORDER_MONTH",  works as expected

SQL> l
  1  CREATE TABLE ORDERS_2026_old (
  2      order_id      NUMBER NOT NULL,
  3      order_date    DATE NOT NULL,
  4      customer_id   NUMBER,
  5      order_month   NUMBER GENERATED ALWAYS AS (TO_NUMBER(TO_CHAR(order_date, 'MM'))) VIRTUAL
  6  )
  7  PARTITION BY RANGE (order_month)
  8  (
  9      PARTITION orders_q1 VALUES LESS THAN (4),  -- Months 1, 2, 3
 10      PARTITION orders_q2 VALUES LESS THAN (7),  -- Months 4, 5, 6
 11      PARTITION orders_q3 VALUES LESS THAN (10), -- Months 7, 8, 9
 12      PARTITION orders_q4 VALUES LESS THAN (13)  -- Months 10, 11, 12
 13* )
SQL> /

Table created.

SQL>

-- using the Expression for the Partitioning Clause

SQL> l
  1  CREATE TABLE ORDERS_2026_NEW (
  2      order_id      NUMBER NOT NULL,
  3      order_date    DATE NOT NULL,
  4      customer_id   NUMBER
  5  )
  6  PARTITION BY RANGE ((TO_NUMBER(TO_CHAR(order_date, 'MM'))))
  7  (
  8      PARTITION orders_q1 VALUES LESS THAN (4),  -- Months 1, 2, 3
  9      PARTITION orders_q2 VALUES LESS THAN (7),  -- Months 4, 5, 6
 10      PARTITION orders_q3 VALUES LESS THAN (10), -- Months 7, 8, 9
 11      PARTITION orders_q4 VALUES LESS THAN (13)  -- Months 10, 11, 12
 12* )
SQL> /

Table created.

SQL>
SQL> insert into orders_2026_new values (1, sysdate, 1);

1 row created.

SQL>
QL> insert into orders_2026_new values (2, to_date('15-JUN-2026','DD-MON-RRRR'),2);

1 row created.

SQL> commit;

Commit complete.

SQL>

-- gather statistics


SQL> exec dbms_stats.gather_table_stats('','ORDERS_2026_NEW');

PL/SQL procedure successfully completed.

SQL>

-- check number of rows in each partition

SQL> col partition_name format a32
SQL> select partition_name, num_rows
  2  from user_tab_partitions
  3  where table_name = 'ORDERS_2026_NEW'
  4  /

PARTITION_NAME                     NUM_ROWS
-------------------------------- ----------
ORDERS_Q1                                 1
ORDERS_Q2                                 1
ORDERS_Q3                                 0
ORDERS_Q4                                 0

SQL>

-- check rows in the specific partitions

-- get all the rows where the month number is less than 4

SQL> select * from orders_2026_new
  2  partition for (3)
  3  /

  ORDER_ID ORDER_DATE         CUSTOMER_ID
---------- ------------------ -----------
         1 15-FEB-26                    1

SQL>

-- get all the rows where the month number is less than 7

SQL> select * from orders_2026_new
  2  partition for (6)
  3  /

  ORDER_ID ORDER_DATE         CUSTOMER_ID
---------- ------------------ -----------
         2 15-JUN-26                    2

SQL>

Since partitioning is by Month Number (would have been the Virtual Column ORDER_MONTH) in the 19c example, I can use  the "PARTITION FOR (<month_number>)"  in my SELECT statement to query a specific partition.

I can confirm that ORDER_ID 1 is in the first partition (Month Number less than 4) and ORDER_ID 2 is in the second partition (Month Number less than 7)