oracle函数 wm_concat 与 listagg

Oraclewm_concat与listagg函数对比分析及风险提示

原创已于 2023-02-03 13:26:27 修改 · 3k 阅读

7 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库

于 2021-07-28 21:03:55 首次发布

Oracle 专栏收录该内容

22 篇文章

订阅专栏

本文对比了Oracle中的wm_concat和listagg函数，wm_concat虽然能实现字符串拼接，但存在非公开函数风险、可能导致临时表空间爆满、锁问题以及与新版本兼容性问题。推荐在11g及以上版本使用listagg，但需注意listagg的字符长度限制。建议了解并谨慎使用这两个函数。

oracle函数 wm_concat 与 listagg

wm_concat

select t.type, to_char(wm_concat(t.id||'---'||t.seq)) as id_seq, to_char(wm_concat(t.seq)) as seqs
from (
	select 'A' type, 'A001' id,  1 seq from dual union all 
	select 'A' type, 'A002' id,  2 seq from dual union all 
	select 'B' type, 'B001' id,  1 seq from dual 
) t group by t.type;

查询结果

listagg

SELECT 
  T.type,
  listagg(T.id ||'---'||T.seq, ',') WITHIN GROUP(ORDER BY T .seq) id_seq,
  listagg(T.seq, ',') WITHIN GROUP(ORDER BY T .seq desc) seqs
FROM (
	select 'A' type, 'A001' id,  1 seq from dual union all 
	select 'A' type, 'A002' id,  2 seq from dual union all 
	select 'B' type, 'B001' id,  1 seq from dual 
) t 
WHERE T.type in ('A', 'B') 
GROUP BY T.type;

查询结果

wm_concat存在问题
1.该函数不是oracle公开的系统函数，它的用户是wmsys,而不是sys或者system，oracle很有可能在版本升级或者补丁的时候取消或者修改这个函数甚至用户，这种变化oracle是不会公开的。所有可能会由于这个变化而导致异常。
2.大量使用这个函数也会导致临时表空间爆满，这是因为在10.2.0.5中，使用wmsys.wm_concat返回的结果格式是CLOB，CLOB占用的临时表空间只有在连接释放后才会释放，部分通过连接池连接数据库的长连接很有可能导致CLOB占用临时表空间不断累积增大，会导致临时表空间爆满的故障。
3.如果是在程序中大量使用这个函数的话会引起enq:TT的锁，可能会导致某些对象被锁。
4.wm_concat在11g中使用需要用to_char()进行转换，否则会出现不兼容现象
建议
oracle11g后推荐使用 listagg 函数，也可以参考wm_concat自己建立一个函数实现相同的行列转换功能

注意：
使用listagg进行分组拼接时，拼接字符过长时会报 ora-01489 错误，造成该报错的主要原因是：oracle对字符变量的长度限制，正常情况下，oracle定义的varchar2类型变量的长度不应超过4000字节