Oracle 23ai 的 JSON 集合分区表

Oracle 23ai 的 JSON 集合分区表

ShawnYan Lv.6

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 环境,这里使用开发者版本。

1
2
3
4
5
6
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 示例:

1
2
3
4
5
6
7
8
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 数据库可以根据分区键的值自动进行分区裁剪。例如,执行以下查询:

1
2
3
4
set autotrace on explain

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

查看执行计划。

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

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

1
2
3
4
5
6
7
---------------------------------------------------------
| Id | Operation | Name | Pstart| Pstop |
---------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | PARTITION RANGE SINGLE| | 2 | 2 |
| 2 | TABLE ACCESS FULL | ORDERS | 2 | 2 |
---------------------------------------------------------

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

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

1
2
3
4
5
6
7
8
9
10
11
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 ~

– / END / –

  • Title: Oracle 23ai 的 JSON 集合分区表
  • Author: ShawnYan
  • Created at: 2025-04-20 23:00:00
  • Updated at: 2025-04-20 23:00:00
  • Link: https://shawnyan.cn/2025/oracle/oracle-23ai-json-partitioned-collection-table/
  • License: This work is licensed under CC BY-NC-SA 4.0.
 Comments