1 2 3 4 Previous Next 49 Replies Latest reply on May 2, 2007 8:47 PM by MichaelS Go to original post
      • 30. Re: Return multi-row result as one comma-delimited row
        SomeoneElse
        Suppose I want to list all the employees for all the departments from the dept table,
        that is including dept=40 which has no employees.
        Which of the other solutions are easily amendable to the above change of the requirements?
        I don't know the model clause (yet), but it's pretty easy with COLLECT. Just do an outer join:
        SQL> SELECT dept.deptno
          2        ,CAST(COLLECT(emp.ename) as coll_type) emps
          3  FROM   emp
          4        ,dept
          5  WHERE  dept.deptno = emp.deptno(+)
          6  GROUP  BY dept.deptno;

                      DEPTNO EMPS
        -------------------- ------------------------------------------------------------------
                          10 COLL_TYPE('CLARK', 'KING', 'MILLER')
                          20 COLL_TYPE('SMITH', 'JONES', 'SCOTT', 'ADAMS', 'FORD')
                          30 COLL_TYPE('ALLEN', 'WARD', 'MARTIN', 'BLAKE', 'TURNER', 'JAMES')
                          40 COLL_TYPE()
        • 31. Re: Return multi-row result as one comma-delimited row
          94799
          Why do we need cast in this, it will return the result without cast also?
          Well as you appear to have shown, you don't.

          Note: having tried your suggestion with the examples here i consistently get ORA-25137: Data value out of range error which I presume means the measure is not of sufficient length.

          Which goes to show how new the MODEL clause is to most of us.
          • 32. Re: Return multi-row result as one comma-delimited row
            Vadim Tropashko-Oracle
            Hmm... Is this result set correct? I assume it is supposed to be:

            DEPTNO EMPS
            ------ ------------------------------------------------------------------
            10 COLL_TYPE('CLARK', 'KING', 'MILLER')
            20 COLL_TYPE('SMITH', 'JONES', 'SCOTT', 'ADAMS', 'FORD')
            30 COLL_TYPE('ALLEN', 'WARD', 'MARTIN', 'BLAKE', 'TURNER', 'JAMES')
            40 COLL_TYPE(NULL)

            because the collection of NULLs is not the same as empty collection.
            • 33. Re: Return multi-row result as one comma-delimited row
              SomeoneElse
              Hmm... Is this result set correct?
              Yeah, it's totally wrong. Oh well, I guess we will have to use your "case closed" solution:
              SQL> with emp_lists (deptno, list, postfix, length) as
                2  (select distinct deptno, '', '', 0
                3  from emp
                4  union all
                5  select e.deptno, list || ', ' || ename, ename, length+1
                6  from emp_lists el, emp e
                7  where el.deptno = e.deptno
                8  and e.ename > el.postfix
                9  )
              10  select deptno, list from emp_lists e
              11  where length = (select max(length)
              12  from emp_lists ee
              13  where e.deptno = ee.deptno);
              with emp_lists (deptno, list, postfix, length) as
                             *
              ERROR at line 1:
              ORA-32033: unsupported column aliasing
              There. That's better.
              • 34. Re: Return multi-row result as one comma-delimited row
                94799
                Vadim was just trying to point out the rather elegant recursive variation of subquery factoring that is supported by (I believe) at least one vendor, but not Oracle, yet.

                Also I don't believe he is having a go at your COLLECT example, just querying whether the result set correctly displays the implied result.
                • 35. Re: Return multi-row result as one comma-delimited row
                  SomeoneElse
                  that is supported by (I believe) at least one vendor, but not Oracle, yet.
                  Well, this is an Oracle forum. So if his solution works with some other vendor, fine but it isn't much use here. (especially when comparing it directly to the MODEL clause).
                  • 36. Re: Return multi-row result as one comma-delimited row
                    Rob van Wijk
                    Here is the tkprof output of the several variants doing string aggregation:
                    ********************************************************************************

                    select deptno
                          , substr(max(sys_connect_by_path(ename, ',' )),2) enames_achter_elkaar
                       from ( select deptno
                                   , ename
                                   , row_number() over (partition by deptno order by ename) rn
                                from grote_emp
                            )
                      start with rn = 1
                    connect by prior rn = rn-1
                        and prior deptno = deptno
                      group by deptno
                      order by deptno

                    call     count       cpu    elapsed       disk      query    current        rows
                    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                    Parse        1      0.00       0.00          0          0          0           0
                    Execute      1      0.00       0.00          0          0          0           0
                    Fetch       31      5.04      10.20       9038       5012          2       30000
                    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                    total       33      5.04      10.20       9038       5012          2       30000

                    Misses in library cache during parse: 0
                    Optimizer mode: ALL_ROWS
                    Parsing user id: 61 

                    Rows     Row Source Operation
                    -------  ---------------------------------------------------
                      30000  SORT GROUP BY (cr=5012 pr=9038 pw=9038 time=8327268 us)
                    140000   CONNECT BY WITH FILTERING (cr=5012 pr=35 pw=35 time=5026711 us)
                      30000    FILTER  (cr=716 pr=0 pw=0 time=823950 us)
                    140000     COUNT  (cr=716 pr=0 pw=0 time=2433935 us)
                    140000      VIEW  (cr=716 pr=0 pw=0 time=1604675 us)
                    140000       WINDOW SORT (cr=716 pr=0 pw=0 time=758165 us)
                    140000        TABLE ACCESS FULL GROTE_EMP (cr=716 pr=0 pw=0 time=560085 us)
                    110000    HASH JOIN  (cr=4296 pr=35 pw=35 time=3891016 us)
                    140000     CONNECT BY PUMP  (cr=0 pr=0 pw=0 time=560076 us)
                    840000     COUNT  (cr=4296 pr=0 pw=0 time=14600811 us)
                    840000      VIEW  (cr=4296 pr=0 pw=0 time=9559357 us)
                    840000       WINDOW SORT (cr=4296 pr=0 pw=0 time=5359294 us)
                    840000        TABLE ACCESS FULL GROTE_EMP (cr=4296 pr=0 pw=0 time=3360438 us)
                          0    COUNT  (cr=0 pr=0 pw=0 time=0 us)
                          0     VIEW  (cr=0 pr=0 pw=0 time=0 us)
                          0      WINDOW SORT (cr=0 pr=0 pw=0 time=0 us)
                          0       TABLE ACCESS FULL GROTE_EMP (cr=0 pr=0 pw=0 time=0 us)


                    Elapsed times include waiting on following events:
                      Event waited on                             Times   Max. Wait  Total Waited
                      ----------------------------------------   Waited  ----------  ------------
                      SQL*Net message to client                      31        0.00          0.00
                      direct path write temp                        478        0.15          1.90
                      direct path read temp                        2485        0.05          2.77
                      SQL*Net message from client                    31        0.01          0.41
                      SQL*Net more data to client                   511        0.00          0.03
                    ********************************************************************************

                    select deptno
                         , stragg(ename) enames_achter_elkaar
                      from grote_emp
                    group by deptno
                    order by deptno

                    call     count       cpu    elapsed       disk      query    current        rows
                    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                    Parse        1      0.00       0.00          0          0          0           0
                    Execute      1      0.00       0.00          0          0          0           0
                    Fetch       31     13.89      13.94          0        716          0       30000
                    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                    total       33     13.89      13.94          0        716          0       30000

                    Misses in library cache during parse: 0
                    Optimizer mode: ALL_ROWS
                    Parsing user id: 61 

                    Rows     Row Source Operation
                    -------  ---------------------------------------------------
                      30000  SORT GROUP BY (cr=716 pr=0 pw=0 time=14028921 us)
                    140000   TABLE ACCESS FULL GROTE_EMP (cr=716 pr=0 pw=0 time=560056 us)


                    Elapsed times include waiting on following events:
                      Event waited on                             Times   Max. Wait  Total Waited
                      ----------------------------------------   Waited  ----------  ------------
                      SQL*Net message to client                      31        0.00          0.00
                      SQL*Net message from client                    31        0.01          0.30
                      SQL*Net more data to client                   511        0.00          0.01
                    ********************************************************************************

                    with t as
                    ( select deptno
                           , substr(ename,2) ename
                        from grote_emp
                       model
                             partition by (deptno)
                             dimension by (row_number() over (partition by deptno order by ename) rn)
                             measures     (cast(ename as varchar2(40)) ename)
                             rules
                             ( ename[any] order by rn = ename[cv()-1] || ',' || ename[cv()]
                             )
                    )
                    select deptno
                         , max(ename) enames_achter_elkaar
                      from t
                    group by deptno
                    order by deptno

                    call     count       cpu    elapsed       disk      query    current        rows
                    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                    Parse        1      0.00       0.00          0          0          0           0
                    Execute      1      0.00       0.00          0          0          0           0
                    Fetch       31      1.96       1.97          0        716          0       30000
                    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                    total       33      1.96       1.97          0        716          0       30000

                    Misses in library cache during parse: 0
                    Optimizer mode: ALL_ROWS
                    Parsing user id: 61 

                    Rows     Row Source Operation
                    -------  ---------------------------------------------------
                      30000  SORT GROUP BY (cr=716 pr=0 pw=0 time=2080124 us)
                    140000   VIEW  (cr=716 pr=0 pw=0 time=1993654 us)
                    140000    SQL MODEL ORDERED (cr=716 pr=0 pw=0 time=1153638 us)
                    140000     WINDOW SORT (cr=716 pr=0 pw=0 time=751592 us)
                    140000      TABLE ACCESS FULL GROTE_EMP (cr=716 pr=0 pw=0 time=560077 us)


                    Elapsed times include waiting on following events:
                      Event waited on                             Times   Max. Wait  Total Waited
                      ----------------------------------------   Waited  ----------  ------------
                      SQL*Net message to client                      31        0.00          0.00
                      SQL*Net message from client                    31        0.01          0.25
                      SQL*Net more data to client                   511        0.00          0.01
                    ********************************************************************************

                    with t as
                    ( select deptno
                           , substr(ename,2) ename
                           , rn
                        from grote_emp
                       model
                             partition by (deptno)
                             dimension by (row_number() over (partition by deptno order by ename desc) rn)
                             measures     (cast(ename as varchar2(4000)) ename)
                             rules
                             ( ename[any] order by rn desc = ename[cv()+1] || ',' || ename[cv()]
                             )
                    )
                    select deptno
                         , ename enames_achter_elkaar
                      from t
                    where rn = 1
                    order by deptno

                    call     count       cpu    elapsed       disk      query    current        rows
                    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                    Parse        1      0.00       0.00          0          0          0           0
                    Execute      1      0.00       0.00          0          0          0           0
                    Fetch       31      7.18     225.50      69363        716          0       30000
                    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                    total       33      7.18     225.50      69363        716          0       30000

                    Misses in library cache during parse: 0
                    Optimizer mode: ALL_ROWS
                    Parsing user id: 61 

                    Rows     Row Source Operation
                    -------  ---------------------------------------------------
                      30000  SORT ORDER BY (cr=716 pr=69363 pw=69363 time=225566028 us)
                      30000   VIEW  (cr=716 pr=69363 pw=69363 time=76049371 us)
                    140000    SQL MODEL ORDERED (cr=716 pr=69363 pw=69363 time=230388256 us)
                    140000     WINDOW SORT (cr=716 pr=0 pw=0 time=1200652 us)
                    140000      TABLE ACCESS FULL GROTE_EMP (cr=716 pr=0 pw=0 time=560075 us)


                    Elapsed times include waiting on following events:
                      Event waited on                             Times   Max. Wait  Total Waited
                      ----------------------------------------   Waited  ----------  ------------
                      SQL*Net message to client                      31        0.00          0.00
                      direct path write temp                       9909        0.50         71.32
                      direct path read temp                        9909        0.31        146.74
                      SQL*Net message from client                    31        0.01          0.33
                      SQL*Net more data to client                   511        0.00          0.01
                    ********************************************************************************

                    with t as
                    ( select deptno
                           , substr(ename,2) ename
                           , rn
                        from grote_emp
                       model
                             partition by (deptno)
                             dimension by (row_number() over (partition by deptno order by ename desc) rn)
                             measures     (cast(ename as varchar2(40)) ename)
                             rules
                             ( ename[any] order by rn desc = ename[cv()+1] || ',' || ename[cv()]
                             )
                    )
                    select deptno
                         , ename enames_achter_elkaar
                      from t
                    where rn = 1
                    order by deptno

                    call     count       cpu    elapsed       disk      query    current        rows
                    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                    Parse        1      0.00       0.00          0          0          0           0
                    Execute      1      0.00       0.00          0          0          0           0
                    Fetch       31      1.85       1.90          0        716          0       30000
                    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                    total       33      1.85       1.90          0        716          0       30000

                    Misses in library cache during parse: 0
                    Optimizer mode: ALL_ROWS
                    Parsing user id: 61 

                    Rows     Row Source Operation
                    -------  ---------------------------------------------------
                      30000  SORT ORDER BY (cr=716 pr=0 pw=0 time=1971450 us)
                      30000   VIEW  (cr=716 pr=0 pw=0 time=885173 us)
                    140000    SQL MODEL ORDERED (cr=716 pr=0 pw=0 time=1205149 us)
                    140000     WINDOW SORT (cr=716 pr=0 pw=0 time=781130 us)
                    140000      TABLE ACCESS FULL GROTE_EMP (cr=716 pr=0 pw=0 time=560075 us)


                    Elapsed times include waiting on following events:
                      Event waited on                             Times   Max. Wait  Total Waited
                      ----------------------------------------   Waited  ----------  ------------
                      SQL*Net message to client                      31        0.00          0.00
                      SQL*Net message from client                    31        0.01          0.25
                      SQL*Net more data to client                   511        0.00          0.01
                    ********************************************************************************

                    select deptno
                         , to_string(cast(collect(ename) as varchar2_ntt)) enames_achter_elkaar
                      from grote_emp
                    group by deptno
                    order by deptno

                    call     count       cpu    elapsed       disk      query    current        rows
                    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                    Parse        1      0.00       0.00          0          0          0           0
                    Execute      1      0.00       0.00          0          0          0           0
                    Fetch       31      1.92       1.91          0        716          0       30000
                    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                    total       33      1.92       1.91          0        716          0       30000

                    Misses in library cache during parse: 0
                    Optimizer mode: ALL_ROWS
                    Parsing user id: 61 

                    Rows     Row Source Operation
                    -------  ---------------------------------------------------
                      30000  SORT GROUP BY (cr=716 pr=0 pw=0 time=999450 us)
                    140000   TABLE ACCESS FULL GROTE_EMP (cr=716 pr=0 pw=0 time=560056 us)


                    Elapsed times include waiting on following events:
                      Event waited on                             Times   Max. Wait  Total Waited
                      ----------------------------------------   Waited  ----------  ------------
                      SQL*Net message to client                      31        0.00          0.00
                      SQL*Net message from client                    31        0.01          0.30
                      SQL*Net more data to client                   511        0.00          0.01
                    ********************************************************************************

                    select deptno
                         , rtrim(extract(xmlagg(xmlelement("X",ename || ',')), '/X/text()'),',') enames_achter_elkaar
                      from grote_emp
                    group by deptno
                    order by deptno

                    call     count       cpu    elapsed       disk      query    current        rows
                    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                    Parse        1      0.00       0.00          0          0          0           0
                    Execute      1      0.00       0.00          0          0          0           0
                    Fetch       31     24.28      24.33          0        719          0       30000
                    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                    total       33     24.28      24.33          0        719          0       30000

                    Misses in library cache during parse: 0
                    Optimizer mode: ALL_ROWS
                    Parsing user id: 61 

                    Rows     Row Source Operation
                    -------  ---------------------------------------------------
                      30000  SORT GROUP BY (cr=716 pr=0 pw=0 time=12429254 us)
                    140000   TABLE ACCESS FULL GROTE_EMP (cr=716 pr=0 pw=0 time=560102 us)


                    Elapsed times include waiting on following events:
                      Event waited on                             Times   Max. Wait  Total Waited
                      ----------------------------------------   Waited  ----------  ------------
                      SQL*Net message to client                      31        0.00          0.00
                      SQL*Net message from client                    31        0.01          0.30
                      SQL*Net more data to client                   511        0.00          0.02



                    ********************************************************************************
                    So, by reversing the order and replacing the aggregation by a simple filter, it indeed made it marginally faster. And the varchar2(4000) variant did a lot of direct path reading and writing to temp. So you have to be careful with that.

                    Comparing the two fastest solutions, model clause and collect, with Tom Kyte's runstats_pkg:
                    SQL> exec runstats_pkg.rs_start

                    PL/SQL-procedure is geslaagd.

                    SQL> with t as
                      2  ( select deptno
                      3         , substr(ename,2) ename
                      4         , rn
                      5      from grote_emp
                      6     model
                      7           partition by (deptno)
                      8           dimension by (row_number() over (partition by deptno order by ename desc) rn)
                      9           measures     (cast(ename as varchar2(40)) ename)
                    10           rules
                    11           ( ename[any] order by rn desc = ename[cv()+1] || ',' || ename[cv()]
                    12           )
                    13  )
                    14  select deptno
                    15       , ename enames_achter_elkaar
                    16    from t
                    17   where rn = 1
                    18   order by deptno
                    19
                    SQL> set termout off
                    SQL> exec runstats_pkg.rs_middle

                    PL/SQL-procedure is geslaagd.

                    SQL> select deptno
                      2       , to_string(cast(collect(ename) as varchar2_ntt)) enames_achter_elkaar
                      3    from grote_emp
                      4   group by deptno
                      5   order by deptno
                      6
                    SQL> set termout off
                    SQL> exec runstats_pkg.rs_stop(10)
                    Run1 draaide in 193 hsecs
                    Run2 draaide in 211 hsecs
                    Run1 draaide in 91,47% van de tijd

                    Naam                                                    Run1      Run2  Verschil
                    LATCH.simulator lru latch                                 67        78        11
                    STAT.calls to kcmgcs                                       4        15        11
                    STAT.cleanout - number of ktugct calls                     4        15        11
                    STAT.active txn count during cleanout                      4        15        11
                    LATCH.simulator hash latch                                68        79        11
                    LATCH.checkpoint queue latch                               5        17        12
                    STAT.workarea executions - optimal                        22         9       -13
                    LATCH.channel operations parent latch                      0        14        14
                    LATCH.undo global data                                     5        19        14
                    STAT.db block changes                                     55        72        17
                    STAT.consistent changes                                   33        50        17
                    STAT.db block gets                                        43        64        21
                    STAT.db block gets from cache                             43        64        21
                    LATCH.shared pool                                         75       104        29
                    STAT.rows fetched via callback                             0        35        35
                    LATCH.JS queue state obj latch                             0        36        36
                    STAT.calls to get snapshot scn: kcmgss                    55        91        36
                    STAT.index fetch by key                                    0        36        36
                    STAT.table fetch by rowid                                  0        37        37
                    LATCH.library cache lock                                  31        83        52
                    STAT.workarea memory allocated                            39       -28       -67
                    STAT.buffer is not pinned count                            1        76        75
                    LATCH.enqueues                                             3        82        79
                    LATCH.enqueue hash chains                                  2        81        79
                    STAT.recursive calls                                     250       145      -105
                    LATCH.library cache pin                                  142       252       110
                    LATCH.SQL memory manager workarea list latch              30       142       112
                    LATCH.row cache objects                                  345       460       115
                    STAT.consistent gets - examination                         4       123       119
                    STAT.consistent gets                                     759       890       131
                    STAT.consistent gets from cache                          759       890       131
                    STAT.session logical reads                               802       954       152
                    LATCH.cache buffers chains                             1,704     1,928       224
                    LATCH.library cache                                      193       441       248
                    STAT.bytes sent via SQL*Net to client              1,030,767 1,030,301      -466
                    STAT.bytes received via SQL*Net from client            3,405     2,751      -654
                    STAT.redo size                                         6,764     5,736    -1,028
                    STAT.undo change vector size                           5,172     4,120    -1,052
                    STAT.sorts (rows)                                    312,341   142,338  -170,003
                    STAT.session pga memory                                    0   196,608   196,608

                    Run1 latches totaal versus run2 -- verschil en percentage
                          Run1      Run2  Verschil     Pct
                         2,826     4,002     1,176  70.61%

                    PL/SQL-procedure is geslaagd.
                    So, the model clause wins.

                    Regards,
                    Rob.
                    • 37. Re: Return multi-row result as one comma-delimited row
                      556287
                      Wow Rob! Great work and analysis once again.
                      I have question though, please don't mind
                      why this?
                      --measures (cast(ename as varchar2(100)) ename)
                      why not this?
                      measures ( ename)
                      just curious.
                      Thank you
                      • 38. Re: Return multi-row result as one comma-delimited row
                        Rob van Wijk
                        Because in my emp table, the ename column is declared as varchar2(10). This cannot contain a string like 'CLARK,KING,MILLER' ...

                        Regards,
                        Rob.
                        • 39. Re: Return multi-row result as one comma-delimited row
                          94799
                          Nice stats Rob :-)

                          Its certainly good to see MODEL holding its own against alternatives, but the VARCHAR2 (4000) thing still concerns me slightly. For unknown length aggregation that is pretty much what you would always want to use, isn't it (assuming we aren't lucky enough to have recursive WITH, of course)?

                          I'm wondering whether if Oracle got around to implementing a built-in equivalent of STRAGG etc. it might perform rather more like COLLECT than a PL/SQL function call.
                          • 40. Re: Return multi-row result as one comma-delimited row
                            556287
                            Thank you, Now I got it.
                            • 41. Re: Return multi-row result as one comma-delimited row
                              MichaelS
                              Good one! That was needed.

                              One more question though: What's that »to_string« function you use in the COLLECT variant?

                              regards, michael
                              • 42. Re: Return multi-row result as one comma-delimited row
                                556287
                                That was from here
                                http://www.oracle-developer.net/display.php?id=306
                                • 43. Re: Return multi-row result as one comma-delimited row
                                  MichaelS
                                  Thanks,

                                  that function is costly part too I guess ...
                                  • 44. Re: Return multi-row result as one comma-delimited row
                                    546595
                                    Once again Rob!
                                    Thank you for the great analysis.
                                    keep it up!!!!