LISTAGGでは4000バイトを超える結果はエラーとなるため、XMLAGGを使えば回避できますが、処理速度はどれぐらいの違いかあるのが気になったので検証してみました。
1万件のデータを準備して検証しています。
LISTAGGを使った検証SQL
SELECT
DEPT
, LISTAGG(NAME, ',')
WITHIN GROUP (ORDER BY NAME) NAME
FROM
TB_SAMPLE
GROUP BY
DEPT
;
XMLAGGを使った検証SQL
SELECT
DEPT
, RTRIM(
XMLAGG(
XMLELEMENT(e, NAME || ',').EXTRACT('//text()')
).GetClobVal(), ','
) AS RESULT
FROM
TB_SAMPLE
GROUP BY
DEPT
;
処理速度結果
| 回数/平均 | LISTAGG | XMLAGG |
|---|---|---|
| 1回目 | 0.21秒 | 1.28秒 |
| 2回目 | 0.21秒 | 1.26秒 |
| 3回目 | 0.21秒 | 1.26秒 |
| 平均 | 0.21秒 | 1.27秒 |
XMLAGGのLISTAGGよりも方が約1秒遅いという結果になりました。
大量データを扱う場合や、高速なレスポンスを要求されるような場合はなるべくLISTAGGを使った方が良さそうです。
SQLの結果が4000バイトを超えないと確証がある場合はLISTAGGを使った方が良いですね。
最後にLISTAGGとXMLAGGの実行計画を確認します。
LISTAGGの実行計画
実行計画
----------------------------------------------------------
Plan hash value: 952551132
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 19M| 12 (9)| 00:00:01 |
| 1 | SORT GROUP BY | | 10000 | 19M| 12 (9)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TB_SAMPLE | 10000 | 19M| 11 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
統計
----------------------------------------------------------
0 recursive calls
0 db block gets
38 consistent gets
0 physical reads
0 redo size
101878 bytes sent via SQL*Net to client
641 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
48 rows processed
XMLAGGの実行計画
実行計画
----------------------------------------------------------
Plan hash value: 952551132
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 19M| 12 (9)| 00:00:01 |
| 1 | SORT GROUP BY | | 10000 | 19M| 12 (9)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TB_SAMPLE | 10000 | 19M| 11 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)
統計
----------------------------------------------------------
0 recursive calls
0 db block gets
38 consistent gets
0 physical reads
0 redo size
39399 bytes sent via SQL*Net to client
31136 bytes received via SQL*Net from client
146 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
48 rows processed
赤枠のところですが、XMLAGGの方が数値が高くなっています。XMLAGGはクライアントとの送受信回数が増えて遅くなってしまうようです。