Oracle 23ai 的 JSON 集合分区表

Oracle 数据库提供了分区表功能，通过将大表拆分为更小、更易于管理的分区来提高性能和可维护性。对于 JSON 数据，Oracle 特别引入了 Partitioned Collection Table（集合分区表）的概念，使我们能够针对 JSON 数据进行有效的分区操作，从而优化查询性能和数据管理。

基础概念

分区表的概念

分区表是将一个大的表按照一定的规则分成多个较小的部分（分区）。每个分区可以独立地进行查询、维护等操作。分区表的优点在于：

• 提高查询性能：通过分区裁剪（Partition Pruning），查询只需要访问相关的分区，减少了数据扫描量。
• 简化维护操作：例如，可以对单个分区进行备份、恢复、重建索引等操作。

JSON 数据的分区

对于 JSON 数据，分区是通过使用 JSON 表达式列作为分区键来实现的。Oracle 提供了两种类型的 JSON 表达式列：

• 虚拟列（Virtual Column）：每次访问列数据时都会重新计算表达式。
• 物化列（Materialized Column）：在 DML 操作时计算表达式值，并将其存储在磁盘上。Oracle 推荐使用物化列来存储经常查询的数据，因为它可以缓存表达式值，提高查询性能。

集合分区表的用法

分区键的定义

分区键必须使用 SQL/JSON 函数 json_value 来定义。这个函数从 JSON 数据中提取标量值。例如，如果我们有一个 JSON 文档中包含字段 PONumber，我们可以使用 json_value(DATA, '$.PONumber') 来提取它的值，并将其用作分区键。

集合分区表的创建

准备 Oracle 23ai 环境，这里使用开发者版本。

SQL> SELECT BANNER_FULL FROM V$VERSION;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04

以下是一个创建集合分区表的 SQL 示例：

CREATE JSON COLLECTION TABLE orders
  (po_num_vc NUMBER GENERATED ALWAYS AS
    (json_value (DATA, '$.PONumber.number()'
     ERROR ON ERROR))
    MATERIALIZED)
  PARTITION BY RANGE (po_num_vc)
   (PARTITION p1 VALUES LESS THAN (1000),
    PARTITION p2 VALUES LESS THAN (2000));

在这个例子中：

• 我们创建了一个名为 orders 的 JSON 集合表。
• po_num_vc 是一个物化表达式列，它使用 json_value 函数从 JSON 数据的 PONumber 字段提取数值。
• 表按照 po_num_vc 的值进行范围分区，p1 分区包含小于 1000 的值，p2 分区包含小于 2000 的值，p3 分区包含小于 3000 的值。

查询与分区裁剪

当执行查询时，Oracle 数据库可以根据分区键的值自动进行分区裁剪。例如，执行以下查询：

set autotrace on explain

SELECT DATA FROM orders p
  WHERE p.data.PONumber.number() = 1234;

查看执行计划。

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

由于分区键 po_num_vc 的值为 2025，落在 p3 分区的范围内，所以执行计划会显示只访问了 p3 分区：

---------------------------------------------------------
| Id  | Operation              | Name   | Pstart| Pstop |
---------------------------------------------------------
|   0 | SELECT STATEMENT       |        |       |       |
|   1 |  PARTITION RANGE SINGLE|        |     2 |     2 |
|   2 |   TABLE ACCESS FULL    | ORDERS |     2 |     2 |
---------------------------------------------------------

知识扩展：作用于 JSON 的三种索引

作用于 JSON 数据类型，常见有三种索引，普通索引，多值索引，检索索引。

SQL> create index ponumber_idx on orders(json_value(data,'$.PONumber.number()' error on error));

Index created.

SQL> create multivalue index p_uppcode_idx on orders p (p.data.PONumber.number());

Index created.

SQL> create search index po_search_idx on orders (data) for json;

Index created.

• 普通索引，对特定字段或属性创建。若 JSON 数据结构经常变化，如字段名或嵌套结构更改，索引可能失效或需重建，增加维护成本。对于结构简单且查询需求明确的 JSON 数据，普通索引是一种有效的索引方式。

• 多值索引（Multivalue Index）是一种特殊的索引类型，主要用于对JSON列中的数组或对象元素进行索引。它与传统的单值索引不同，单值索引每行只能有一个索引值，而多值索引允许多个索引记录对应同一主键或行。当需要频繁查询JSON数组中的特定值时，多值索引可以避免全表扫描，优化查询性能。

• Search Index：在处理 JSON 数据时，Search Index 是一种专门用于提升查询性能的索引类型。它与传统索引不同，特别适用于 JSON 数据的复杂查询场景，能够对 JSON 文档中的文本内容进行索引，支持模糊匹配、词干提取、同义词扩展等全文搜索功能。例如，可以在产品描述中搜索包含特定关键词的文档。

总结

Oracle 的集合分区表功能为 JSON 数据的存储和查询提供了一个强大的工具。通过合理地定义分区键和分区策略，我们可以显著提高查询性能和数据管理效率。分区裁剪技术可以减少数据扫描量，而物化表达式列则可以提高查询响应速度。在设计分区方案时，需要综合考虑数据的访问模式、插入频率以及存储资源等因素，以达到最佳的平衡点。

Have a nice day ~ ☕

🌻 往期精彩 ▼

• 错过一个亿，MySQL免费认证页面下架了
• MySQL 30 周年庆！MySQL 8.4 认证免费考！这次是认真的。。。
• 卷疯了！众数据库厂商的征文汇
• TiDB社区&墨天轮 | 专栏征文大赛全面开启，期待您分享TiDB业务实战和运维开发那些事儿
• 「合集」三年 50 篇，TiDB 干货全收录
• 「合集」MySQL 8.x 系列文章汇总
• TiDB 新朋友 DBdoctor
• Oracle 数据库全面升级为 23ai
• 广东的崖山，中国的崖山数据库
• TiDB v8 发版！超硬核 v8 引擎！
• 几张图带你了解 TiDB 架构演进
• 一文带你了解 KING BASE 金仓数据库
• 全球 Oracle ACE 社区突破 500 位成员
• 如何选择适合的 MySQL Connector/J 版本
• 即将告别 PG 12，建议升级到 PG 16.3 版本
• G-Star Landscape 2.0 重磅发布，助力开源生态再升级
• 【一文讲透（番外篇）】如何编译安装KWDB v2.0.4数据库
• TiDB x DeepSeek 打造更好用的国产知识库问答系统解决方案

– / END / –

👉 这里可以找到我

• 微信公众号：@少安事务所 #少安事务所
• ITPUB：@少安事务所
• TiDB 专栏：@ShawnYan
• PGFans: @严少安
• 墨天轮：@严少安

👉 这里有得聊

如果对国产基础软件（操作系统、数据库、中间件）感兴趣，可以加群一起聊聊。
关注微信公众号：少安事务所，后台回复[群]，即可看到入口。

如果这篇文章为你带来了灵感或启发，请帮忙『三连』吧，感谢！ღ( ´･ᴗ･` )~