Oracle Cloud DB 19c JSON_ARRAYAGG多列聚合排序异常问题

• Oracle聚合函数默认不保证继承子查询的排序结果，哪怕子查询已经完成排序，优化器处理多列聚合时可能调整行访问顺序，仅第一个聚合函数的遍历顺序和子查询排序匹配，后续聚合函数会触发内部反向扫描优化，最终出现仅首元素位置正确、剩余元素倒序的问题，这是Oracle 19c JSON函数处理的已知非预期行为。
• 你写的外层ORDER BY b.date_rw是针对聚合后的结果行排序，不是聚合过程中元素的排序规则，完全不生效。

正确做法是在每个JSON_ARRAYAGG函数内部显式指定排序规则，不要依赖子查询的排序：

SELECT json_object(
         'buy' VALUE JSON_ARRAYAGG(b.buysum ORDER BY b.date_rw DESC),
         'total' VALUE JSON_ARRAYAGG(b.totalsum ORDER BY b.date_rw DESC)
       )
FROM   (
  select *
  from   view_count_sum
  ORDER BY date_rw DESC
  FETCH FIRST 10 ROWS ONLY
) b;

如果仍出现顺序异常，可在子查询添加/*+ NO_MERGE */Hint阻止子查询和外层聚合的执行计划合并：

SELECT json_object(
         'buy' VALUE JSON_ARRAYAGG(b.buysum ORDER BY b.date_rw DESC),
         'total' VALUE JSON_ARRAYAGG(b.totalsum ORDER BY b.date_rw DESC)
       )
FROM   (
  select /*+ NO_MERGE */ *
  from   view_count_sum
  ORDER BY date_rw DESC
  FETCH FIRST 10 ROWS ONLY
) b;

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