Oracle 19C listagg支持distinct

Oracle 19C listagg后可直接加入distinct关键字，可对内容进行去重，原来要达到这个效果只能在里面加一个子查询先去重，再拼接，测试发现在性能上有很大提升，另外SQL简化代码也显得优雅。
测试表数据如下：
[code]SQL> select * from par_hybrid_tab;

ID NAME
---------- -----
1 jsss
2 kjjj
3 looo
16 mppp
16 mppp
28 gyyy
39 arrr

7 rows selected.[/code]
正常拼接：
[code]SQL> select listagg(name,';') within group (order by id) from par_hybrid_tab;

LISTAGG(NAME,';')WITHINGROUP(ORDERBYID)
---------------------------------------------------------------------------------------------------------------
jsss;kjjj;looo;mppp;mppp;gyyy;arrr[/code]
老版本去重拼接：
[code]SQL> select listagg(name,';') within group (order by id) from (select distinct id,name from par_hybrid_tab);

LISTAGG(NAME,';')WITHINGROUP(ORDERBYID)
---------------------------------------------------------------------------------------------------------------
jsss;kjjj;looo;mppp;gyyy;arrr[/code]
新版本listagg后加distinct关键字直接去重：
[code]SQL> select listagg(distinct name,';') within group (order by id) from par_hybrid_tab;

LISTAGG(DISTINCTNAME,';')WITHINGROUP(ORDERBYID)
---------------------------------------------------------------------------------------------------------------
jsss;kjjj;looo;mppp;gyyy;arrr[/code]
对比两者的执行计划：
[code]SQL> explain plan for select listagg(distinct name,';') within group (order by id) from par_hybrid_tab;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
Plan hash value: 2231853357

------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 30 (0)| 00:00:01 | | |
| 1 | SORT GROUP BY | | 1 | 17 | | | | |
| 2 | PARTITION RANGE ALL | | 8415 | 139K| 30 (0)| 00:00:01 | 1 | 4 |
| 3 | TABLE ACCESS HYBRID PART FULL| PAR_HYBRID_TAB | 8415 | 139K| 30 (0)| 00:00:01 | 1 | 4 |
| 4 | TABLE ACCESS FULL | PAR_HYBRID_TAB | | | | | 1 | 4 |
------------------------------------------------------------------------------------------------------------------

11 rows selected.

SQL> explain plan for select listagg(name,';') within group (order by id) from (select distinct id,name from par_hybrid_tab);

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1318975795

----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | | 79 (2)| 00:00:01 | | |
| 1 | SORT GROUP BY | | 1 | 17 | | | | | |
| 2 | PARTITION RANGE ALL | | 8415 | 139K| | 79 (2)| 00:00:01 | 1 | 4 |
| 3 | VIEW | | 8415 | 139K| | 79 (2)| 00:00:01 | | |
| 4 | HASH UNIQUE | | 8415 | 139K| 240K| 79 (2)| 00:00:01 | | |
| 5 | TABLE ACCESS HYBRID PART FULL| PAR_HYBRID_TAB | 8415 | 139K| | 30 (0)| 00:00:01 | 1 | 4 |
| 6 | TABLE ACCESS FULL | PAR_HYBRID_TAB | | | | | | 1 | 4 |
----------------------------------------------------------------------------------------------------------------------------

13 rows selected.[/code]