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)

The new FILTER clause in Aggregations

 Oracle 26.1 introduces the new FILTER clause that can be used in aggregations.  

Thus, for example, in a SUM aggregation you can use FILTER WHERE ...

Here is a quick example :

SQL> create table obj_list as select * from dba_objects;

Table created.

SQL> select sum(object_id) from obj_list;

SUM(OBJECT_ID)
--------------
    2604221816

SQL> select object_type, sum(object_id) from obj_list group by object_type order by 1;

OBJECT_TYPE             SUM(OBJECT_ID)
----------------------- --------------
CLUSTER                           3036
CONSUMER GROUP                  435231
CONTEXT                        1048288
...... deleted a number of object types
SEQUENCE                       7551346
SYNONYM                      473741064
TABLE                         44661426   --> sum of object_ids for object_type TABLE
TABLE PARTITION               15047252
TABLE SUBPARTITION              603760
...... deleted a few more object types
XML SCHEMA                      511732

46 rows selected.


SQL> select sum(object_id) filter (where object_type = 'TABLE')  as Table_ObjID_Sum from obj_list;

TABLE_OBJID_SUM
---------------
       44661426


SQL>

For more information see : 

1. Oracle Documentation New Features Guide: Aggregation Filters

2. Oracle Documentation SQL For Application Developers : 14.2 Aggregation Filters 

3. Markus Winand's Modern SQL Reference : Filter

Video Demonstration of Retrieval (in RAG)

I have created a Video Demo of the Retrieval for RAG, based on what I have posted here blog posts.