Oracle已创建JSON索引但查询仍全表扫描的原因排查

以下是导致Oracle执行全表扫描而非使用INDEX_ONE索引的常见原因：

• 索引列与查询条件的类型不匹配
  索引中$.id对应的是JSON_VALUE返回的VARCHAR2(300)类型，但查询里将其CAST为INTEGER后进行>1的比较，这种类型转换会导致Oracle无法直接利用索引上的id列进行过滤——索引存储的是字符串值，而查询需要数值比较逻辑，优化器会判定索引无法高效满足该条件。

• 索引不具备覆盖性，回表成本过高
  查询的内层SELECT中包含了key1、json_col1、created以及$.date1、$.date2这些列，但INDEX_ONE索引仅包含6个JSON_VALUE提取的字段，未覆盖上述列。若使用索引，Oracle需先通过索引定位符合条件的行，再回表读取额外列的数据。当优化器估算回表的IO成本高于全表扫描时，会倾向于选择全表扫描。

• NOT JSON_EXISTS条件的基数估算偏差
  查询中的NOT JSON_EXISTS(json_col1, '$?( (@.jsonElement1 in ("NAME")) )')属于反向过滤条件，Oracle优化器对这类NOT条件的基数估算往往存在偏差。如果优化器判断该条件过滤后剩余数据量较大，会认为全表扫描比索引扫描更高效。

• 统计信息过期或不准确
  若表或索引的统计信息长时间未更新，优化器无法准确评估数据分布、过滤后行数等关键信息，可能错误选择全表扫描。比如表中符合条件的实际行数极少，但统计信息显示行数庞大，优化器就会倾向于全表扫描。

• 复合索引的前导列基数问题
  INDEX_ONE是复合索引，前导列是jsonElement1对应的JSON_VALUE字段。如果该列基数极低（比如大部分行的jsonElement1都是"NAME"），那么NOT JSON_EXISTS过滤后剩余的数据量很大，优化器会判定索引扫描效率不如全表扫描。

• JSON路径或索引定义的细节差异
  需仔细核对以下细节是否完全一致：

  • JSON_EXISTS中的路径与索引中JSON_VALUE的路径（包括大小写、下划线、嵌套结构）
  • 索引中定义了ERROR ON ERROR NULL ON EMPTY，但查询中的JSON_VALUE和JSON_EXISTS未指定这些子句，Oracle可能认为两者语义不完全匹配，无法复用索引。

• ORDER BY与ROWNUM的组合限制
  内层查询需要按CAST(JSON_VALUE(json_col1, '$.id') AS INTEGER)排序后取前10条，但索引中存储的$.id是VARCHAR2类型，无法直接用于数值排序，优化器无法利用索引避免排序操作。若优化器估算排序+索引扫描的总成本高于全表扫描+排序，就会选择全表扫描。

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