Oracle 19C层级JSON查询优化：替代循环嵌套SQL的方案咨询

当前遍历table_1逐行查询table_2的做法属于N+1查询，数据量大时性能会很差，有两种更优的替代方案：

方案一：用Oracle原生JSON函数直接生成目标JSON #

Oracle 12c及以上版本支持原生JSON生成能力，只需一次SQL查询就能直接输出符合要求的层级结构，Java端拿到结果直接用就行，不用再做数据组装。

示例SQL #

SELECT JSON_ARRAYAGG(
         JSON_OBJECT(
           'id' VALUE t1.ID,
           'name' VALUE t1.name,
           'url' VALUE t1.url,
           'target' VALUE (
             SELECT JSON_ARRAYAGG(
                      JSON_OBJECT(
                        'id1' VALUE t2.id1,
                        'col1' VALUE t2.col1,
                        'col2' VALUE t2.col2
                      )
                    )
             FROM table_2 t2
             WHERE t2.target_id = t1.target_id
           )
         )
       ) AS result_json
FROM table_1 t1;

方案二：Java端批量查询+内存组装 #

如果不想依赖Oracle的JSON特性，可以通过两次批量查询+内存组装来优化：

• 第一步：一次性查出table_1的所有数据，用target_id作为键把数据存入Map，方便快速查找。
• 第二步：一次性查出table_2的所有数据，遍历每条记录，根据target_id找到对应的table_1记录，把table_2的数据添加到其target列表里。
• 第三步：把table_1的列表序列化为JSON。

核心Java伪代码 #

// 1. 批量查询table_1所有数据
List<Table1Entity> table1List = table1Mapper.selectAll();
Map<String, Table1Entity> targetIdMap = table1List.stream()
    .collect(Collectors.toMap(Table1Entity::getTargetId, e -> e));

// 2. 批量查询table_2所有数据并关联
List<Table2Entity> table2List = table2Mapper.selectAll();
table2List.forEach(table2 -> {
    Table1Entity table1 = targetIdMap.get(table2.getTargetId());
    if (table1 != null) {
        table1.getTargetList().add(table2);
    }
});

// 3. 序列化为JSON
String jsonResult = objectMapper.writeValueAsString(table1List);

优化后的优势 #

• 大幅减少数据库交互次数：原方案是N+1次查询（N是table_1的行数），优化后最多2次查询，降低数据库压力。
• 提升整体性能：减少网络IO和连接占用，避免频繁查询带来的性能损耗。

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