为何JSON数组列创建的索引在关联查询中未被数据库使用？

问题背景 #

你的Acts表中BasedOn列存储JSON数组数据，格式示例：

[{"Id": "1", "Name": "Business Law"}, ...]

你尝试创建多个索引优化查询未被其他条目引用的记录，但查询始终无法利用这些索引。相关无效索引及更新后的查询语句如下：

无效索引1 #

CREATE MULTIVALUE INDEX "IX_MyIndex1" ON "Acts" A (A."BasedOn".Id.string());

无效索引2 #

CREATE INDEX "IX_MyIndex2" ON "Acts" A (JSON_QUERY(A."BasedOn", '$.Id.string()'));

更新后的查询语句 #

SELECT "OfficialId", "ActArea"
  FROM "Acts" A1
 WHERE "Changes" IS NULL
   AND NOT EXISTS (
       SELECT 1 FROM "Acts" A2, (JSON_TABLE(A2."BasedOn", '$[*]'
           ERROR ON ERROR NULL ON EMPTY NULL ON MISMATCH
                   COLUMNS(
                           Id VARCHAR2(20) PATH '$.Id'
                       ))) JT WHERE JT.Id = A1."OfficialId");  

尝试的第三个索引 #

CREATE MULTIVALUE INDEX "IX_MyIndex3" ON "Acts"
       (JSON_TABLE("BasedOn", '$[*]'
                   ERROR ON ERROR NULL ON EMPTY NULL ON MISMATCH
                   COLUMNS(
                           Id NVARCHAR2(20) PATH '$.Id'
                       )));

索引无效的原因 #

1. IX_MyIndex1：JSON数组访问逻辑错误
   直接用A."BasedOn".Id.string()无法遍历JSON数组内的所有元素，该语法仅适用于单个JSON对象的属性访问，无法提取数组中每个对象的Id值，导致索引存储内容不符合查询需求。

2. IX_MyIndex2：JSON_QUERY使用场景错误
   JSON_QUERY用于提取JSON片段而非标量值，它返回的是包含多个Id的JSON数组，而非单个Id字符串。查询中是用单个OfficialId与Id做等值匹配，索引存储内容与查询条件不匹配，因此无法被利用。

3. IX_MyIndex3：数据类型不匹配
   索引中JSON_TABLE定义的Id列类型为NVARCHAR2(20)，但查询语句中JSON_TABLE的Id列是VARCHAR2(20)，数据类型不一致会导致数据库无法匹配索引。此外，若数据库版本低于Oracle 12cR2，可能不支持基于JSON_TABLE的多值索引。

解决办法 #

方案1：修正多值索引的类型匹配 #

将IX_MyIndex3的Id列类型改为与查询一致的VARCHAR2(20)：

CREATE MULTIVALUE INDEX "IX_MyIndex3" ON "Acts"
(
  JSON_TABLE("BasedOn", '$[*]'
             ERROR ON ERROR NULL ON EMPTY NULL ON MISMATCH
             COLUMNS(
               Id VARCHAR2(20) PATH '$.Id'
             ))
);

方案2：使用JSON_VALUE创建多值索引 #

针对数组中的每个Id值创建多值索引，语法更简洁：

CREATE MULTIVALUE INDEX "IX_BasedOn_Ids" ON "Acts"
(
  JSON_VALUE("BasedOn" FORMAT JSON, '$[*].Id' ERROR ON ERROR NULL ON EMPTY NULL ON MISMATCH)
);

辅助优化步骤 #

1. 收集表统计信息
   确保数据库拥有最新的表和索引统计信息，帮助优化器正确选择执行计划：

   EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => 'Acts', CASCADE => TRUE);
   

2. 强制使用索引（可选）
   若优化器仍选择全表扫描，可在子查询中添加索引提示：

   SELECT "OfficialId", "ActArea"
     FROM "Acts" A1
    WHERE "Changes" IS NULL
      AND NOT EXISTS (
          SELECT 1 FROM "Acts" A2 /*+ INDEX(A2 IX_MyIndex3) */, 
          (JSON_TABLE(A2."BasedOn", '$[*]'
              ERROR ON ERROR NULL ON EMPTY NULL ON MISMATCH
                      COLUMNS(
                              Id VARCHAR2(20) PATH '$.Id'
                          ))) JT WHERE JT.Id = A1."OfficialId");  
   

内容的提问来源于stack exchange，提问作者Desperado