Thanks for the question, Ashu.

Asked: October 13, 2016 - 3:00 pm UTC

Last updated: October 17, 2016 - 12:44 am UTC

Version: 11.2

Viewed 10K+ times! This question is

You Asked

What are the technical differences between WM_CONCAT vs LISTAGG? I know WM_CONCAT is undocumented function. Is LISTAGG technically superior or do they do the same thing very differently?

and Chris said...

Technical differences are irrelevant. WM_CONCAT is undocumented, so you shouldn't use it!

Or when you upgrade you may find things unexpectedly break:

SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 14 09:11:11 2016

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

Last Successful login time: Fri Oct 14 2016 09:06:36 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SP2-0158: unknown SET option "sqlformat"
SQL> with rws as (
  2    select rownum x from dual connect by level <= 10
  3  )
  4    select wm_concat(x) from rws;
  select wm_concat(x) from rws
         *
ERROR at line 4:
ORA-00904: "WM_CONCAT": invalid identifier

That's right, wm_concat is gone in 12c...

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

LISTAGG technically superior ?!?!?

Rajeshwaran, Jeyabal, October 14, 2016 - 10:48 am UTC

Is LISTAGG technically superior or do they do the same thing very differently?

Yes, LISTAGG is superior than WM_CONCAT. (less latches than wm_concat, remember latches are light weight locks that impact the scalability, the more latches we do, the less the scalability is)

rajesh@ORA11G> create or replace package demo_pkg
  2  as
  3     procedure use_listagg;
  4     procedure use_wmconcat;
  5  end;
  6  /

Package created.

rajesh@ORA11G> create or replace package body demo_pkg
  2  as
  3     procedure use_listagg
  4     as
  5             l_data long;
  6     begin
  7             for i in 1..10000
  8             loop
  9                     select listagg(user_id) within group(order by username)
 10                     into l_data
 11                     from all_users
 12                     where dbms_random.random is not null;
 13             end loop;
 14     end;
 15
 16     procedure use_wmconcat
 17     as
 18             l_data long;
 19     begin
 20             for i in 1..10000
 21             loop
 22                     select wm_concat(user_id)
 23                     into l_data
 24                     from all_users
 25                     where dbms_random.random is not null;
 26             end loop;
 27     end;
 28  end;
 29  /

Package body created.

rajesh@ORA11G> exec runstats_pkg.rs_start;

PL/SQL procedure successfully completed.

rajesh@ORA11G> exec demo_pkg.use_wmconcat;

PL/SQL procedure successfully completed.

rajesh@ORA11G> exec runstats_pkg.rs_middle;

PL/SQL procedure successfully completed.

rajesh@ORA11G> exec demo_pkg.use_listagg;

PL/SQL procedure successfully completed.

rajesh@ORA11G> exec runstats_pkg.rs_stop(100);
Run1 ran in 1362 hsecs
Run2 ran in 502 hsecs
run 1 ran in 271.31% of the time

Name                                  Run1        Run2        Diff
LATCH.SQL memory manager worka      40,271      40,137        -134
LATCH.messages                         204          50        -154
LATCH.enqueue hash chains              264          96        -168
STAT...table fetch by rowid        420,201     420,012        -189
STAT...consistent gets - exami         310           2        -308
STAT...no work - consistent re     530,339     530,012        -327
LATCH.row cache objects            120,800     120,369        -431
STAT...recursive calls              10,535      10,051        -484
STAT...buffer is not pinned co      70,553      70,024        -529
STAT...recursive cpu usage           1,180         479        -701
STAT...CPU used by this sessio       1,195         493        -702
STAT...CPU used when call star       1,196         493        -703
STAT...Elapsed Time                  1,370         523        -847
STAT...DB time                       1,406         509        -897
LATCH.checkpoint queue latch         1,260         161      -1,099
STAT...hot buffers moved to he       1,360           0      -1,360
LATCH.cache buffers lru chain        2,543           1      -2,542
STAT...free buffer inspected         2,671           0      -2,671
LATCH.object queue header heap       2,808           9      -2,799
STAT...table scan rows gotten      426,534     420,000      -6,534
STAT...sorts (memory)                    4      10,000       9,996
STAT...lob writes                   10,000           0     -10,000
STAT...lob writes unaligned         10,000           0     -10,000
STAT...lob reads                    10,000           0     -10,000
STAT...free buffer requested        10,045          11     -10,034
STAT...consistent gets from ca     580,329     570,021     -10,308
STAT...consistent gets             580,662     570,023     -10,639
STAT...consistent gets from ca     580,662     570,023     -10,639
STAT...workarea executions - o           6      20,002      19,996
LATCH.object queue header oper      22,977          29     -22,948
STAT...db block changes             40,086          73     -40,013
STAT...consistent changes           40,070          56     -40,014
STAT...calls to get snapshot s      50,159      10,007     -40,152
STAT...db block gets               120,071          50    -120,021
STAT...db block gets from cach     120,071          50    -120,021
STAT...session logical reads       700,733     570,073    -130,660
LATCH.Memory Queue                 287,378     100,359    -187,019
STAT...session pga memory          196,608           0    -196,608
STAT...physical read total byt     262,144           0    -262,144
STAT...physical read bytes         262,144           0    -262,144
STAT...cell physical IO interc     262,144           0    -262,144
LATCH.post/wait queue              431,221     150,532    -280,689
LATCH.cache buffers chains       1,491,462   1,140,292    -351,170
STAT...sorts (rows)                     11     420,000     419,989
STAT...session uga memory          431,768           0    -431,768
STAT...file io wait time           522,223           0    -522,223
STAT...temp space allocated (b   1,048,576           0  -1,048,576
STAT...session pga memory max    1,376,256           0  -1,376,256
STAT...session uga memory max    1,473,328           0  -1,473,328
LATCH.shared pool                3,677,391   1,286,120  -2,391,271
LATCH.kwqbsn:qsga                7,181,806   2,512,972  -4,668,834
LATCH.Shared B-Tree              7,466,197   2,616,197  -4,850,000
STAT...logical read bytes from####################################

Run1 latches total versus runs -- difference and pct
        Run1        Run2        Diff       Pct
  20,797,405   8,037,678 -12,759,727    258.75%

PL/SQL procedure successfully completed.

rajesh@ORA11G>
rajesh@ORA11G>
rajesh@ORA11G>

Also LISTAGG is superior than STRAGG (user defined analytics/aggregates using ODCI)

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4844411400346525471