胖头鱼的技术专栏-477 26ai新特性实战：表达式分区（20261013）

作者：胖头鱼的鱼缸（尹海文）
Oracle ACE Pro: Database
PostgreSQL ACE
10年+数据库行业经验
拥有OCM 11g/12c/19c、MySQL 8.0 OCP、Exadata、CDP等认证
墨天轮MVP，ITPUB认证专家
圈内拥有“总监”称号，非著 名社恐（社交恐怖分子）
全网同名：胖头鱼的鱼缸
ITPUB：yhw1809
除授权转载并标明出处外，均为“非法”抄袭

特性介绍

Oracle AI Database 23.26.1 引入了表达式分区（Partition by Expression）功能。此前，要基于表达式进行分区（如对日期进行截断后再分区），必须先创建一个虚拟列作为分区键，然后在分区子句中引用该虚拟列。

Partition by Expression 消除了这一中间步骤，允许开发者直接在 PARTITION BY 子句中使用 SQL 表达式定义分区键。这简化了 Schema 设计，减少了 DDL 编写的复杂性，同时使分区定义更加直观和表达力更强。

语法对比

传统方式（需虚拟列）：

CREATE TABLE sales (
  sale_id   NUMBER,
  sale_date DATE,
  amount    NUMBER,
  qtr       GENERATED ALWAYS AS (TRUNC(sale_date, 'Q'))
) PARTITION BY RANGE (qtr) (
  PARTITION p_q1 VALUES LESS THAN (DATE '2026-04-01'),
  ...
);

表达式分区（新方式）：

CREATE TABLE sales (
  sale_id   NUMBER,
  sale_date DATE,
  amount    NUMBER
) PARTITION BY RANGE (TRUNC(sale_date, 'Q')) (
  PARTITION p_q1 VALUES LESS THAN (DATE '2026-04-01'),
  ...
);

支持的表达式类型：

• 日期分割：TRUNC(date_col, ‘Q’), EXTRACT(YEAR FROM date_col)
• 字符串提取：SUBSTR(string_col, 1, 2)
• JSON 字段：json_value(json_col, ‘$.field’)
• 数学运算：CEIL(amount/1000) * 1000
• 任何确定性 SQL 表达式

适用场景

• 时间序列数据：按年、季度、月或周对事实表进行分区
• 租户隔离：基于 JSON 字段中的 tenant_id 进行分区
• 数据分段：按金额区间对交易记录进行分区
• 逻辑与物理分离：分区键由业务表达式定义，而非物理列

实战演示

同之前一样，使用用户NFTEST，已授权DB_DEVELOPER_ROLE角色，测试操作均在NFTEST用户下执行。

测试1：日期表达式分区

CREATE TABLE pbe_sales (
  sale_id   NUMBER,
  product   VARCHAR2(100),
  sale_date DATE,
  amount    NUMBER
) PARTITION BY RANGE (TRUNC(sale_date, 'Q')) (
  PARTITION p_q1 VALUES LESS THAN (DATE '2026-04-01'),
  PARTITION p_q2 VALUES LESS THAN (DATE '2026-07-01'),
  PARTITION p_q3 VALUES LESS THAN (DATE '2026-10-01'),
  PARTITION p_q4 VALUES LESS THAN (DATE '2027-01-01'),
  PARTITION p_max VALUES LESS THAN (MAXVALUE)
);

测试2：插入数据并查询

INSERT INTO pbe_sales VALUES (1, 'Widget', DATE '2026-01-15', 100);
INSERT INTO pbe_sales VALUES (2, 'Gadget', DATE '2026-04-10', 200);
INSERT INTO pbe_sales VALUES (3, 'Widget', DATE '2026-07-20', 150);
INSERT INTO pbe_sales VALUES (4, 'Gadget', DATE '2026-11-05', 300);
COMMIT;
SELECT sale_id, product, sale_date, amount
FROM pbe_sales
ORDER BY sale_id;

测试3：分区结构验证

SELECT table_name, partition_name
FROM all_tab_partitions
WHERE table_owner = 'NFTEST' AND table_name = 'PBE_SALES'
ORDER BY partition_position;

测试4：分区裁剪

SELECT sale_id, product, amount
FROM pbe_sales
WHERE TRUNC(sale_date, 'Q') = DATE '2026-01-01'
ORDER BY sale_id;

测试5：EXTRACT(YEAR FROM date) 分区

CREATE TABLE pbe_orders (
  order_id    NUMBER,
  customer    VARCHAR2(100),
  order_date  DATE,
  total       NUMBER
) PARTITION BY RANGE (EXTRACT(YEAR FROM order_date)) (
  PARTITION p_2025 VALUES LESS THAN (2026),
  PARTITION p_2026 VALUES LESS THAN (2027),
  PARTITION p_2027 VALUES LESS THAN (2028),
  PARTITION p_max VALUES LESS THAN (MAXVALUE)
);
INSERT INTO pbe_orders VALUES (101, 'AAA Corp', DATE '2025-06-15', 5000);
INSERT INTO pbe_orders VALUES (102, 'BBB Ltd',  DATE '2026-03-10', 8000);
INSERT INTO pbe_orders VALUES (103, 'CCC Inc',  DATE '2026-09-20', 3000);
COMMIT;
SELECT order_id, customer, order_date, total
FROM pbe_orders
ORDER BY order_id;

总结

本期对 Oracle AI Database 26ai，23.26.1 版本新引入的表达式分区进行了完整介绍与实战演示。

老规矩，知道写了些啥。