Oracle如何为JSON_TABLE生成的虚拟列建索引以降低JOIN成本？

能否为JSON_TABLE生成的虚拟列创建索引？ #

不能直接为JSON_TABLE输出的虚拟列创建索引，因为这些列是查询时动态生成的临时结果集，不属于任何物理表的持久化列。但可以通过以下间接方式实现类似效果，同时优化JOIN成本：

优化JOIN成本的具体方法 #

1. 为country_map的关联列创建索引

当前执行计划中country_map做了全表扫描，给关联字段code建索引能让JOIN过程更快定位数据：

CREATE INDEX idx_country_map_code ON country_map(code);

2. 为customers表的JSON字段创建JSON搜索索引

Oracle的JSON搜索索引专门针对JSON数据优化，能加速JSON_TABLE对数组的解析和字段提取，避免全表扫描后再逐行解析JSON：

CREATE SEARCH INDEX idx_customers_json ON customers(doc) FOR JSON;

3. 使用物化视图预先展开JSON数组

如果customers表的JSON数据更新不频繁，可以创建物化视图预先将JSON数组展开为关系型行结构，再给物化视图的关联字段建索引：

-- 创建物化视图
CREATE MATERIALIZED VIEW mv_customers_json
AS
SELECT 
    row_number,
    region,
    cntry
FROM 
    customers,
    JSON_TABLE(doc, '$[*]' COLUMNS 
           (
               row_number FOR ORDINALITY,
               region     VARCHAR2(10) PATH '$.REGION',
               cntry      VARCHAR2(10) PATH '$.CNTRY'
           )) t;

-- 给物化视图的cntry列建索引
CREATE INDEX mv_idx_customers_cntry ON mv_customers_json(cntry);

之后查询可直接关联物化视图，避免每次查询都解析JSON：

SELECT 
    region, code, description 
FROM 
    mv_customers_json t
JOIN 
    country_map m ON m.code = t.cntry;

4. 使用查询提示强制优化执行计划

如果Oracle优化器没有自动选择最优连接方式，可以用提示强制使用索引或指定连接类型：

SELECT /*+ INDEX(m idx_country_map_code) USE_NL(t m) */
    region, code, description 
FROM 
    customers,
    JSON_TABLE(doc, '$[*]' COLUMNS 
           (
               row_number FOR ORDINALITY,
               region     VARCHAR2(10) PATH '$.REGION',
               cntry      VARCHAR2(10) PATH '$.CNTRY'
           )) t
JOIN 
    country_map m ON m.code = t.cntry;

USE_NL(t m)表示使用嵌套循环连接，适合小表关联的场景；INDEX(m idx_country_map_code)强制使用指定索引。

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