listagg如果用作条件表达式中的窗口函数，则生成ORA-01489

Question

我的查询返回许多(数千)行。列l对于非常少量的行(最多10行)具有一定的值。对于每个这样的行，我想在所有这些行上输出非常短(最多5个字符)的varchar列v的聚合逗号分隔值。对于没有特殊值l的行，我想简单地输出该行的v值。

同一问题的综合示例:从前10000个整数中，我想为每个个位数输出1,2,3,4,5,6,7,8,9;该号码为多位数。(是的，愚蠢的例子，但实际情况是有意义的。)

with x (v,l) as (
select to_char(level), length(to_char(level)) from dual connect by level <= 10000
)
select case l
when 1 then listagg(v,',') within group (order by v) over (partition by l)
else v
end
from x
order by 1;

问题是listagg函数在ORA-01489: result of string concatenation is too long错误时失败。

我知道listagg函数的4000个字符限制以及基于xmllag的解决方案。我只是不明白这个极限对于我想要连接的数据是足够的尽管对于所有的数据是不够的。在上面的例子中，9个个位数的分区可以容纳4000个字符，而9000个四位数的分区则不能。我期望case表达式会阻止不相关行的窗口执行，但是，出于某种原因，似乎db引擎评估所有行的窗口。(另请注意，order by子句会导致查询快速失败——如果没有它，在失败之前会返回一些行。)

你能解释一下这种行为的原因吗?我怀疑窗口计算在逻辑上先于select条款，但没有任何证据。在Oracle 11g, 18c和19 (livesql)上复制。

Answer 1

你正在使用SQL，这不是程序，所以你不能期望代码路径的某些部分将不会被执行，仅仅是因为它们没有被使用。(因此，像其他人建议的那样填补错误将不会成功)。

无论如何，您可以根据listagg忽略null值的事实执行常用的技巧。

所以这个公式很好:

with x (v,l) as (
select to_char(level), length(to_char(level)) from dual connect by level <= 10000
)
select   nvl(listagg(case when l = 1 then v end,',') within group (order by v) over (partition by l),v) lst
from x
order by 1;

为

LST
------------------
1,2,3,4,5,6,7,8,9
1,2,3,4,5,6,7,8,9
..
10
100
1000
10000

问题的解释可以在执行计划中找到(只显示相关部分)

----------------------------------------------------------------------------------------
| Id  | Operation                       | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |      |     1 |    35 |     4  (50)| 00:00:01 |
|   1 |  SORT ORDER BY                  |      |     1 |    35 |     4  (50)| 00:00:01 |
|   2 |   WINDOW SORT                   |      |     1 |    35 |     4  (50)| 00:00:01 |
|   3 |    VIEW                         |      |     1 |    35 |     2   (0)| 00:00:01 |
|*  4 |     CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   5 |      FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
...
Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - (#keys=1) CASE "L" WHEN 1 THEN LISTAGG("V",',') WITHIN GROUP ( ORDER BY 
"V") OVER ( PARTITION BY "L") ELSE "V" END [4000]
2 - (#keys=2) "L"[NUMBER,22], "V"[VARCHAR2,40], LISTAGG("V",',') WITHIN 
GROUP ( ORDER BY "V") OVER ( PARTITION BY "L")[4000]
3 - "V"[VARCHAR2,40], "L"[NUMBER,22]
4 - LEVEL[4]

因此，在第2行中，listagg被计算(所有行)，仅在第1行中被过滤。

Answer 2

奇怪的是，即使没有结果超过4000个字符，您也会得到关于4000个字符限制的错误。也许你可以把它作为bug提交给Oracle支持。

另一个解决方法是使用LISTAGG函数的ON OVERFLOW逻辑，如果您在Oracle 12.2或更高版本上。在查询中使用LISTAGG (v, ',' ON OVERFLOW TRUNCATE)允许查询在没有错误的情况下运行，并且不会截断任何值(至少在示例中)。