Bo's Oracle Station › 博客首页 ›Oracle技术› 开发和基础管理 › 查看内容

【博客文章2025】在Oracle数据库中为JSON数据创建B树函数索引和位图函数索引，并观察其执行计划

2025-1-18 18:33| 发布者: admin| 查看: 25| 评论: 0|原作者: Bo Tang

摘要: 本博客介绍如何使用JSON_VALUE运算符创建B树函数索引以及如何使用JSON_EXISTS运算符创建位图函数索引。对于谓词是筛选JSON数据中某个标量键值的查询（例如：按照ID查找帖子)，B树函数索引可以提供较高的查询和索引性能。如果该键值的取值范围是少数几个离散值，则位图函数索引可以提供更高的查询和索引性能。通过查看执行计划，我们可以验证以上两种索引有效地被使用到。

Author: Bo Tang

1. 在Oracle数据库中为JSON数据创建索引:

使用下面数据字典视图来查询Oracle数据库中总共有多少JSON列，以便创建JSON索引。

select *from DBA_JSON_COLUMNS where owner='HR';

OWNER	TABLE_NAME	OBJECT_TYPE	COLUMN_NAME	FORMAT	DATA_TYPE
HR	J1	TABLE	DOC	TEXT	CLOB

2. 使用JSON_VALUE运算符创建B树函数索引:

对于谓词是筛选JSON数据中某个标量键值的查询（例如：按照ID查找帖子)，B树函数索引可以提供较高的查询和索引性能。如果该键值的取值范围是少数几个离散值，则位图函数索引可以提供更高的查询和索引性能。

查看一下实验数据（实验环境来自https://www.botangdb.com/mytec/mytec_basicadmin/202501/00900132.html）：

SELECT id, JSON_VALUE(doc, '$.Requestor') Requestor, JSON_VALUE (doc, '$."Special Instructions"') SpecInstr
FROM j1 order by id;

ID	REQUESTOR	SPECINSTR
1	赵先生	平寄邮件
2
3
4	钱先生

进行基于JSON_VALUE的严选查询（ERROR ON ERROR）：

SELECT JSON_VALUE (doc, '$.Number'   returning NUMBER(10) ERROR ON ERROR ) NUM
FROM j1 ;

ORA-40462: JSON 路径 '$.Number' 的求值结果为无值。
40462. 00000 - "JSON_VALUE evaluated to no value"
*Cause:    The provided JavaScript Object Notation (JSON) path expression
           did not select a value.
*Action:   Correct the JSON path expression.

由于在表中的ID为2和3的两行数据中，找不到该JSON键值，所以报告ORA-40462错误。在创建B树函数索引时，也一样报错：

CREATE UNIQUE INDEX i_j1
ON j1 (JSON_VALUE(doc, '$.Number'
returning NUMBER(10) ERROR ON ERROR));

在行: 10 上开始执行命令时出错 -
CREATE UNIQUE INDEX i_j1
ON j1 (JSON_VALUE(doc, '$.Number'
returning NUMBER(10) ERROR ON ERROR))
错误报告 -
ORA-40462: JSON 路径 '$.Number' 的求值结果为无值。
40462. 00000 - "JSON_VALUE evaluated to no value"
*Cause:    The provided JavaScript Object Notation (JSON) path expression
           did not select a value.
*Action:   Correct the JSON path expression.

删除表中ID为2和3的两行数据：

delete from j1 where id=2;

delete from j1 where id=3;

commit;

SELECT JSON_VALUE (doc, '$.Number' returning NUMBER(10) ERROR ON ERROR ) NUM
FROM j1 ;

NUM
4
1

创建B树函数索引成功：

CREATE UNIQUE INDEX i_j1
ON j1 (JSON_VALUE(doc, '$.Number'
returning NUMBER(10) ERROR ON ERROR));

INDEX I_J1 已创建。

创建唯一型（ERROR ON ERROR）B树函数索引后，向JSON列插入空值会成功，而向JSON列插入‘{}’不会成功：

insert into j1 values ( 2, null);
1行已插入。
commit;

insert into j1 values(3, '{}');

在行: 40 上开始执行命令时出错 -
insert into j1 values(3, '{}')
错误报告 -
ORA-40462: JSON 路径 '$.Number' 的求值结果为无值。
ORA-61721: 由于 JSON 查询出现错误，索引维护失败

用autotrace查看执行计划和统计信息：

[oracle@station1 ~]$ sqlplus /nolog

SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Sun Jan 19 21:07:05 2025
Version 23.5.0.24.07

Copyright (c) 1982, 2024, Oracle. All rights reserved.

SQL> conn hr/cloud_4U@pdb1_1
Connected.
SQL> set linesize 1000
SQL> set autot on

SELECT id, JSON_VALUE(doc, '$.Requestor') Requestor, JSON_VALUE (doc, '$."Special Instructions"') SpecInstr
FROM hr.j1 WHERE JSON_VALUE (doc,'$.Number'
returning NUMBER(10)) = 4;

Execution Plan
----------------------------------------------------------
Plan hash value: 3929295065

-------------------------------------------------------------------------------------
| Id | Operation             | Name | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |        |    4 | 5268 |    4   (0)| 00:00:01 |
|   1 | NESTED LOOPS             |        |    4 | 5268 |    4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| J1   |    1 | 1313 |    1   (0)| 00:00:01 |
|* 3 |    INDEX UNIQUE SCAN         | I_J1 |    1 |        |    0   (0)| 00:00:01 |
|   4 |   JSONTABLE EVALUATION         |        |    4 |    16 |    3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access(JSON_VALUE("DOC" /*+ LOB_BY_VALUE */ FORMAT JSON , '$.Number'
          RETURNING NUMBER(10,0) ERROR ON ERROR TYPE(LAX) )=4)

Note
-----
   - dynamic statistics used: dynamic sampling (level=AUTO (SYSTEM))

Statistics
----------------------------------------------------------
      0 recursive calls
      0 db block gets
      2 consistent gets
      0 physical reads
      0 redo size
   785 bytes sent via SQL*Net to client
   108 bytes received via SQL*Net from client
      2 SQL*Net roundtrips to/from client
      0 sorts (memory)
      0 sorts (disk)
      1 rows processed

访问路径是INDEX UNIQUE SCAN，有效使用到唯一型（ERROR ON ERROR）B树函数索引。

3. 位图函数索引：使用JSON_EXISTS运算符：

CREATE BITMAP INDEX ibmp_j1
ON j1 (JSON_EXISTS(doc,'$.CostCenter'));

INDEX I_J1 已创建。

用autotrace查看执行计划和统计信息：

SELECT id, JSON_VALUE(doc, '$.Requestor') Requestor, JSON_VALUE (doc, '$."Special Instructions"') SpecInstr
FROM hr.j1 WHERE JSON_VALUE(doc,'$.CostCenter')= 'A';

Execution Plan
----------------------------------------------------------
Plan hash value: 4064502479

------------------------------------------------------------------------------------------------
| Id | Operation                 | Name    | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |     1 | 1317 |    15    (0)| 00:00:01 |
|   1 | NESTED LOOPS                 |           |     1 | 1317 |    15    (0)| 00:00:01 |
|* 2 |   TABLE ACCESS BY INDEX ROWID BATCHED| J1      |     1 | 1313 |    12    (0)| 00:00:01 |
|   3 |    BITMAP CONVERSION TO ROWIDS         |           |       |       |        |           |
|   4 |     BITMAP INDEX FULL SCAN         | IBMP_J1 |       |       |        |           |
|   5 |   JSONTABLE EVALUATION             |           |     4 |    16 |     3    (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(JSON_VALUE("DOC" /*+ LOB_BY_VALUE */ FORMAT JSON , '$.CostCenter'
          RETURNING VARCHAR2(4000) NULL ON ERROR TYPE(LAX) )='A')

Statistics
----------------------------------------------------------
   26 recursive calls
      0 db block gets
   26 consistent gets
      0 physical reads
      0 redo size
   862 bytes sent via SQL*Net to client
   108 bytes received via SQL*Net from client
      2 SQL*Net roundtrips to/from client
      0 sorts (memory)
      0 sorts (disk)
      2 rows processed

访问路径是BITMAP INDEX FULL SCAN，有效使用到位图函数索引。

4. 两种索引的数据字典信息：

select index_name, index_type, uniqueness, compression, leaf_blocks, distinct_keys, status ,global_stats, funcidx_status
from DBA_INDEXES where owner='HR' and table_name='J1';

INDEX_NAME	INDEX_TYPE	UNIQUENESS	COMPRESSION	LEAF_BLOCKS	DISTINCT_KEYS	STATUS	GLOBAL_STATS	FUNCIDX_STATUS
SYS_IL0000083153C00002$$	LOB	UNIQUE	DISABLED			VALID	NO
I_J1	FUNCTION-BASED NORMAL	UNIQUE	DISABLED	1	2	VALID	YES	ENABLED
IBMP_J1	FUNCTION-BASED BITMAP	NONUNIQUE	DISABLED	1	2	VALID	YES	ENABLED

路过

雷人

握手

鲜花

鸡蛋

收藏邀请

上一篇：【博客文章2025】使用JSON_TABLE和NESTED PATH运算符构造所需结果集