Oracle AI Database 26ai lets you place an expression directly in the partitioning clause. You no longer need to expose a virtual column only to use its result as the partition key.

Supported partitioning methods

Partition by expression works with the basic RANGE, LIST and HASH methods. Oracle creates and manages a hidden virtual column internally for each expression.

Test environment

All examples were verified using SQL*Plus on:

Oracle AI Database 26ai Free Release 23.26.3.0.0
Version 23.26.3.0.0

RANGE partitioning by expression

This table is partitioned by the month calculated from order_date:

CREATE TABLE expr_sales
(
  order_id   NUMBER PRIMARY KEY,
  order_date DATE NOT NULL,
  amount     NUMBER(10,2) NOT NULL
)
PARTITION BY RANGE (TRUNC(order_date, 'MM'))
(
  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_future  VALUES LESS THAN (MAXVALUE)
);

INSERT INTO expr_sales VALUES (1, DATE '2026-01-10', 120);
INSERT INTO expr_sales VALUES (2, DATE '2026-01-25', 80);
INSERT INTO expr_sales VALUES (3, DATE '2026-02-05', 200);
INSERT INTO expr_sales VALUES (4, DATE '2026-03-18', 150);
INSERT INTO expr_sales VALUES (5, DATE '2026-05-01', 300);
COMMIT;

Check the physical partition used by every row:

SELECT s.order_id,
       TO_CHAR(s.order_date, 'YYYY-MM-DD') order_date,
       s.amount,
       o.subobject_name partition_name
FROM expr_sales s
JOIN user_objects o
  ON o.data_object_id = DBMS_ROWID.ROWID_OBJECT(s.rowid)
ORDER BY s.order_id;

Result:

ORDER_ID ORDER_DATE AMOUNT PARTITION_NAME
-------- ---------- ------ --------------
       1 2026-01-10    120 P_2026_01
       2 2026-01-25     80 P_2026_01
       3 2026-02-05    200 P_2026_02
       4 2026-03-18    150 P_2026_03
       5 2026-05-01    300 P_FUTURE

Use the same expression in the predicate to enable partition pruning:

EXPLAIN PLAN FOR
SELECT SUM(amount)
FROM expr_sales
WHERE TRUNC(order_date, 'MM') = DATE '2026-02-01';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(
  NULL, NULL, 'BASIC +PARTITION'
));

The tested plan contains:

| Operation              | Pstart | Pstop |
|------------------------|--------|-------|
| PARTITION RANGE SINGLE |      2 |     2 |
| TABLE ACCESS FULL      |      2 |     2 |

LIST partitioning by expression

The expression normalizes region codes, so values such as pl and PL use the same partition:

CREATE TABLE expr_customers
(
  customer_id   NUMBER PRIMARY KEY,
  region_code   VARCHAR2(2) NOT NULL,
  customer_name VARCHAR2(30) NOT NULL
)
PARTITION BY LIST (UPPER(region_code))
(
  PARTITION p_emea     VALUES ('PL', 'DE', 'FR'),
  PARTITION p_americas VALUES ('US', 'CA'),
  PARTITION p_other    VALUES (DEFAULT)
);

INSERT INTO expr_customers VALUES (1, 'pl', 'Anna');
INSERT INTO expr_customers VALUES (2, 'US', 'John');
INSERT INTO expr_customers VALUES (3, 'jp', 'Yuki');
COMMIT;

Check row placement:

SELECT c.customer_id,
       c.customer_name,
       c.region_code,
       o.subobject_name partition_name
FROM expr_customers c
JOIN user_objects o
  ON o.data_object_id = DBMS_ROWID.ROWID_OBJECT(c.rowid)
ORDER BY c.customer_id;

Result:

CUSTOMER_ID CUSTOMER_NAME REGION_CODE PARTITION_NAME
----------- ------------- ----------- --------------
          1 Anna          pl          P_EMEA
          2 John          US          P_AMERICAS
          3 Yuki          jp          P_OTHER

Check partition pruning:

EXPLAIN PLAN FOR
SELECT customer_name
FROM expr_customers
WHERE UPPER(region_code) = 'PL';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(
  NULL, NULL, 'BASIC +PARTITION'
));

The tested plan contains:

| Operation             | Pstart | Pstop |
|-----------------------|--------|-------|
| PARTITION LIST SINGLE | KEY    | KEY   |
| TABLE ACCESS FULL     |      1 |     1 |

HASH partitioning by expression

This table distributes rows using the calculated net amount:

CREATE TABLE expr_items
(
  item_id  NUMBER PRIMARY KEY,
  price    NUMBER(10,2) NOT NULL,
  discount NUMBER(10,2) NOT NULL
)
PARTITION BY HASH (price - discount)
(
  PARTITION p_h1,
  PARTITION p_h2,
  PARTITION p_h3,
  PARTITION p_h4
);

INSERT INTO expr_items VALUES (1, 100, 10);
INSERT INTO expr_items VALUES (2, 200, 20);
INSERT INTO expr_items VALUES (3, 150, 0);
INSERT INTO expr_items VALUES (4, 80, 5);
COMMIT;

Check row placement:

SELECT i.item_id,
       i.price - i.discount net_amount,
       o.subobject_name partition_name
FROM expr_items i
JOIN user_objects o
  ON o.data_object_id = DBMS_ROWID.ROWID_OBJECT(i.rowid)
ORDER BY i.item_id;

Result from the tested database:

ITEM_ID NET_AMOUNT PARTITION_NAME
------- ---------- --------------
      1         90 P_H2
      2        180 P_H2
      3        150 P_H2
      4         75 P_H4

Hash assignment is calculated internally and can differ if the partition definition changes. Applications should not depend on a particular partition name.

Check partition pruning:

EXPLAIN PLAN FOR
SELECT item_id
FROM expr_items
WHERE price - discount = 90;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(
  NULL, NULL, 'BASIC +PARTITION'
));

The tested plan contains:

| Operation             | Pstart | Pstop |
|-----------------------|--------|-------|
| PARTITION HASH SINGLE |      2 |     2 |
| TABLE ACCESS FULL     |      2 |     2 |

Check the hidden virtual columns

SELECT table_name,
       column_name,
       hidden_column,
       virtual_column,
       data_default
FROM user_tab_cols
WHERE table_name IN
  ('EXPR_SALES', 'EXPR_CUSTOMERS', 'EXPR_ITEMS')
  AND hidden_column = 'YES'
ORDER BY table_name, column_id;

The generated ORA_PART_KEY_... column names are system-defined and can differ between databases.

When to use an explicit virtual column

Use partition by expression when the derived value is needed only for data placement. Use an explicit virtual column when applications also need that value for queries, reports or indexes.

Restrictions

• The restrictions are the same as for virtual column-based partitioning.
• External tables are not supported because they do not support virtual columns.
• PL/SQL function calls cannot be used in a virtual-column partitioning key.
• If an update can move a row between partitions, enable row movement first.

Cleanup

DROP TABLE expr_sales PURGE;
DROP TABLE expr_customers PURGE;
DROP TABLE expr_items PURGE;

Summary

Oracle 26ai supports expressions with RANGE, LIST and HASH partitioning. The syntax stays compact, while matching predicates can still prune the scan to a single partition.

More details are available in the Oracle VLDB and Partitioning Guide and the Oracle AI Database 26ai New Features Guide.

Have a fun 🙂

Tomasz