Elapsed: 00:29:54.60 Execution Plan - Plan hash value: 2522236266 - | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | - | 0 | SELECT STATEMENT | | 287K| 7860K| | 5030 (2)| 00:01:01 | | 1 | SORT GROUP BY | | 287K| 7860K| 30M| 5030 (2)| 00:01:01 | | 2 | TABLE ACCESS FULL| KP_AGG_TEST | 876K| 23M| | 1137 (2)| 00:00:14 | - Statistics - 51 recursive calls 3449842 db block gets 323291 consistent gets 6328 physical reads 0 redo size 103733023 bytes sent via SQL*Net to client 41398092 bytes received via SQL*Net from client 862457 SQL*Net roundtrips to/from client 0 sorts (memory) 1 sorts (disk) 287485 rows processedĪs you can see, WM_CONCAT took longer than List Agg. Elapsed: 00:00:43.59 Execution Plan - Plan hash value: 2522236266 -– Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | -– 0 | SELECT STATEMENT | | 287K| 7860K| | 5030 (2)| 00:01:01 | 1 | SORT GROUP BY | | 287K| 7860K| 30M| 5030 (2)| 00:01:01 | 2 | TABLE ACCESS FULL| KP_AGG_TEST | 876K| 23M| | 1137 (2)| 00:00:14 | -– Statistics - 67 recursive calls 20 db block gets 4077 consistent gets 8320 physical reads 0 redo size 11625136 bytes sent via SQL*Net to client 134407 bytes received via SQL*Net from client 19167 SQL*Net roundtrips to/from client 0 sorts (memory) 1 sorts (disk)Ģ87485 rows processed DEV> select id, wm_concat(paramlist) from KP_AGG_TEST 2 group by id 287485 rows selected.

I used same huge data set and same environment.įirst look at ListAgg Vs WM_Concat DEV> select id, listagg(paramlist,’ ’) within group (order by paramlist) as agg_data 2 from KP_AGG_TEST 3 group by id 287485 rows selected. Some of those are STRAGG and 10g introduced undocumented function WM_CONCAT.īelow I illustrated the differences in performance using these different function.

In prior versions we used different ways to achieve same results. One of the Oracle Database 11g features is to provide concatenated data for each group called LISTAGG.

Listagg oracle 10g