12C新特性：WM_CONCAT函数的替代方式（1）

从Oracle 12.1.0.1起，将禁用WM_CONCAT函数。官方推荐使用LISTAGG函数替代

为什么要改用选择LISTAGG？
官方的回答为：LISTAGG优于WM_CONCAT，它的闩锁少于wm_concat。本文的目的在于测试LISTAGG能否替代WM_CONCAT。测试环境为19.3与11.2.0.4之间的比对。

1、19.3上测试

先测试 WM_CONCAT是否可用：

[oracle@whs1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jan 19 15:26:36 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> SELECT wmsys.WM_CONCAT(TYPE) from v$parameter;
SELECT wmsys.WM_CONCAT(TYPE) from v$parameter
       *
ERROR at line 1:
ORA-00904: "WMSYS"."WM_CONCAT": invalid identifier

用listagg替换测试

SQL> SELECT listagg(TYPE,',')  from v$parameter;

LISTAGG(TYPE,',')
--------------------------------------------------------------------------------
2,3,3,1,3,1,3,3,1,2,3,3,2,2,2,6,2,1,3,3,1,2,1,6,6,6,6,6,6,3,3,6,2,2,1,2,2,2,2,2,
2,2,2,2,2,2,2,2,2,2,2,2,1,1,3,1,2,2,3,1,1,2,2,3,3,2,2,3,3,3,3,2,1,2,2,2,1,2,1,3,
3,2,1,6,6,6,2,2,2,3,3,2,2,3,6,6,6,6,6,6,6,6,6,2,2,3,2,2,2,6,2,2,2,2,2,2,2,2,2,2,
2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,
2,2,2,2,2,2,2,2,2,2,2,2,2,2,1,2,2,3,2,2,3,3,2,2,3,3,2,3,6,3,3,3,3,3,1,1,3,2,2,2,
2,2,2,2,6,2,1,3,2,3,3,1,2,3,1,1,3,6,1,2,1,3,1,3,3,1,2,2,2,3,2,3,3,2,6,6,2,2,1,2,
2,2,2,2,2,2,2,2,3,3,2,2,2,2,2,2,1,2,1,1,1,2,3,1,1,1,2,3,2,2,2,1,3,3,3,2,2,3,3,3,
3,3,1,2,2,2,2,2,1,3,2,2,1,2,2,1,3,2,2,1,1,2,3,1,3,3,3,2,2,3,3,2,3,3,2,6,3,3,2,2,
2,2,2,2,2,2,2,1,3,3,1,3,3,3,2,2,2,2,2,2,3,3,1,1,2,2,2,2,2,3,4,1,2,2,1,1,2,2,1,3,
3,3,2,2,6,2,3,2,2,1,1,1,3,1,1,1,1,2,2,1,1,1,3,2,1,1,1,2,1,1,2,1,1,2,2,2,3,1,1,6,
6,2,1,2,3,1,2,1,2,2,6,2,2,2,1,3,3,2,3,1,1,2,2,2,1,2,1,2,2,1,2,3,3,1,2,2,2,2,6,3,
2,2,2,3,2,2

listagg也能支持distinct，如下：

SQL> SELECT listagg(distinct TYPE,',')  from v$parameter;

LISTAGG(DISTINCTTYPE,',')
--------------------------------------------------------------------------------
1,2,3,4,6

2、用11.2.0.4测试

[oracle@whs ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 19 15:29:13 2021

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SET LONG 10000000 
SQL> set pagesize 200
SQL> SELECT WM_CONCAT(TYPE) from v$parameter;

WM_CONCAT(TYPE)
--------------------------------------------------------------------------------
2,3,3,1,3,1,3,3,3,2,2,6,2,1,3,3,1,1,2,6,6,6,6,6,3,3,2,2,2,2,2,2,2,2,2,2,2,2,2,2,
2,2,2,2,2,2,2,3,1,1,1,1,3,1,3,2,2,1,3,3,6,6,6,2,2,2,3,3,2,2,3,6,6,6,6,6,6,6,6,3,
2,2,2,6,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,
2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,1,2,2,3,2,2,2,3,2,1,2,2,
3,3,3,3,3,3,3,1,1,1,3,3,2,2,2,2,2,2,2,6,2,1,3,3,3,1,2,3,1,3,3,1,3,3,2,2,2,3,2,3,
3,2,2,2,2,2,2,2,1,2,3,1,1,1,1,2,3,1,2,2,1,3,3,3,2,2,2,3,3,3,3,3,2,2,2,1,3,2,2,2,
1,2,2,1,3,2,2,1,1,2,3,3,3,3,2,2,3,3,2,3,3,6,3,3,2,2,2,2,2,2,2,3,3,3,3,3,2,2,2,2,
2,2,3,3,1,1,2,2,2,2,2,3,4,1,2,2,1,1,2,2,1,3,1,1,3,3,2,2,6,2,3,2,2,1,1,3,1,1,1,1,
2,2,1,1,1,3,2,2,3,1,1,6,6,3,1,2,1,2,2,6,2,2,2,3,2,3,2,2,2,2,1,2

使用distinct测试

SQL> SELECT WM_CONCAT(DISTINCT TYPE) from v$parameter;

WM_CONCAT(DISTINCTTYPE)
--------------------------------------------------------------------------------
1,2,3,4,6

测试listagg

SQL> SELECT listagg(TYPE,',')  from v$parameter;
SELECT listagg(TYPE,',')  from v$parameter
                          *
ERROR at line 1:
ORA-02000: missing WITHIN keyword

11.2.0.4不支持以上这种写法，需要加上within group (order by xxx) ，测试如下

SQL>  select listagg( TYPE,',') within group (order by type) from v$parameter;

LISTAGG(TYPE,',')WITHINGROUP(ORDERBYTYPE)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,
2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,
2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,4,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6

3、结果对比

将19.3的 listagg(TYPE,',') 的结果与 11.2.0.4的WM_CONCAT(TYPE)结果对比，结果是相似的，不同点是WM_CONCAT的结果是clob，listagg的结果是varchar。所以有字符型上需要做修改；且字段长度是不一样的，listagg长度不够有可能会满足不了需求。如下就会报错：

SQL> SELECT listagg(object_id,',')  from dba_objects;
SELECT listagg(object_id,',')  from dba_objects
                                    *
ERROR at line 1:
ORA-01489: result of string concatenation is too long