Oracle DB 21c查询JSON数据时索引未被选用问题咨询

核心原因 #

• 表达式不匹配：你创建索引用的是JSON_VALUE(json_document, '$.site_name' RETURNING VARCHAR2 ERROR ON ERROR)，但查询里的过滤条件是针对JSON_TABLE生成的虚拟列site_name。Oracle优化器没法自动关联这两个表达式——前者是直接对CLOB列调用JSON函数，后者是JSON_TABLE解析后的结果，优化器默认不会认为二者完全等价，因此不会触发索引。
• 统计信息过时：如果表或索引的统计信息没及时更新，优化器无法准确判断索引的性价比，可能会默认选择全表扫描。尤其是数据量变化大的时候，旧统计信息会误导优化器。
• 索引选择性太差：如果site_name = 'My Site'的结果集占总数据的比例过高（比如超过20%），优化器会觉得走索引再回表的成本比直接全表扫描更高，自然不会选索引。

解决办法 #

1. 调整查询，直接匹配索引表达式 #

把过滤条件改成直接调用JSON_VALUE，让优化器能识别到索引：

SELECT
    d.site_name,
    d.orderid
FROM
    orders ct,
    JSON_TABLE ( ct.json_document, '$'
            COLUMNS 
                site_name VARCHAR2 PATH '$.site_name',
                orderid NUMBER PATH '$.order.orderid'            
        ) d
WHERE JSON_VALUE(ct.json_document, '$.site_name' RETURNING VARCHAR2 ERROR ON ERROR) = 'My Site';

2. 用虚拟列统一表达式 #

先给表加一个对应site_name的虚拟列，再基于虚拟列建索引（和你之前的索引逻辑一致，但更直观）：

-- 添加虚拟列
ALTER TABLE orders ADD site_name_vc AS (JSON_VALUE(json_document, '$.site_name' RETURNING VARCHAR2 ERROR ON ERROR)) VIRTUAL;
-- 建索引
CREATE INDEX orders_sitename_vc_idx ON orders(site_name_vc);

之后查询直接用虚拟列过滤：

SELECT
    d.site_name,
    d.orderid
FROM
    orders ct,
    JSON_TABLE ( ct.json_document, '$'
            COLUMNS 
                site_name VARCHAR2 PATH '$.site_name',
                orderid NUMBER PATH '$.order.orderid'            
        ) d
WHERE ct.site_name_vc = 'My Site';

3. 更新统计信息 #

强制收集表和索引的最新统计信息，让优化器能准确评估成本：

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

4. 强制使用索引（临时方案） #

如果以上方法都没效果，可以用提示强制走索引，但不推荐作为长期方案——数据变化后可能失效：

SELECT /*+ INDEX(ct orders_sitename_idx) */
    d.site_name,
    d.orderid
FROM
    orders ct,
    JSON_TABLE ( ct.json_document, '$'
            COLUMNS 
                site_name VARCHAR2 PATH '$.site_name',
                orderid NUMBER PATH '$.order.orderid'            
        ) d
WHERE site_name = 'My Site';

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