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

我的查询返回许多(数千)行。 列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个字符限制以及基于xmlagg的解决方法。我只是没有得到我想要连接的数据的限制是足够的，即使不是对所有数据都足够。在上面的示例中，9个一位数的分区可以容纳4000个字符，而9000个四位数的分区则不能。我原以为case表达式会阻止执行不相关行的窗口，但由于某种原因，数据库引擎似乎对所有行的窗口求值。(还要注意，order by子句导致查询快速失败-如果没有它，一些行将在失败前返回。)

你能解释一下这种行为的原因吗？我怀疑窗口计算逻辑上在select子句之前，但没有任何证据。在Oracle 11g、18c和19(Livesql)上复制。

---

解决方案

好吧，您使用的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行中筛选listagg。