Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Martijn.

Asked: August 08, 2013 - 9:08 pm UTC

Last updated: August 12, 2013 - 4:02 pm UTC

Version: 10g compatible

Viewed 1000+ times

You Asked

Hi Tom,

Just wondering on how you would solve this:
Aggregate strings, but limited by a certain number of separators.
Here's the original question:
https://forums.oracle.com/thread/2567472

Assuming you also are still having EMP around, albeit on 12c ;) ,
you already have the table.
My plan was to limit the aggregation rightaway, through connect_by_isleaf, but I'm missing the point somewhere or it cannot be done through CONNECT BY logic?

select deptno

,      ename

,      rn

,      rn2

,      replace(mod(rn, 3), 0, 3)

--,      level lvl

--,      ltrim(sys_connect_by_path( ename,','), ',') str

--,      connect_by_root  ename

--,      connect_by_isleaf

from (

       select deptno

       ,      ename

       ,      mgr

       ,      row_number() over (partition by deptno order by deptno, ename) rn

       ,      ceil(row_number() over (partition by deptno order by deptno, ename)/3) rn2

       from   emp

     )

/*where connect_by_isleaf=1

start with rn = 1

connect by replace(mod(rn, 3), 0, 3) = prior replace(mod(rn, 3), 0, 3)+1

       and rn2 = prior rn2

       and deptno = prior deptno*/


But a comment on the original thread would even be nicer ;)


Regards,
Martijn

and Tom said...

ops$tkyte%ORA11GR2> select rtrim(
  2         max( decode( mod(rownum-1,3), 0, ename ))||','||
  3         max( decode( mod(rownum-1,3), 1, ename ))||','||
  4         max( decode( mod(rownum-1,3), 2, ename )), ',' )
  5    from scott.emp
  6   group by floor( rownum/3-.1)
  7   order by floor( rownum/3-.1)
  8  /

RTRIM(MAX(DECODE(MOD(ROWNUM-1,3)
--------------------------------
SMITH,ALLEN,WARD
JONES,MARTIN,BLAKE
CLARK,SCOTT,KING
TURNER,ADAMS,JAMES
FORD,MILLER


Rating

  (8 ratings)

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

Comments

Wow

Martijn Hoekstra, August 08, 2013 - 10:21 pm UTC

You rock!

nice !

Sokrates, August 09, 2013 - 7:41 am UTC


Great !!!

A reader, August 09, 2013 - 2:24 pm UTC


Adding deptno: change of behavior

Hoek, August 09, 2013 - 2:57 pm UTC

Tom,

Great technique, but after adding deptno to the query, I get a result my brain is unable (or: incapable?) to parse.

Can you explain what's happening here, is this resultset to be expected:

SQL> select deptno
  2  ,      rtrim( max( decode( mod(rownum-1,3), 0, ename ))||','||
  3                max( decode( mod(rownum-1,3), 1, ename ))||','||
  4                max( decode( mod(rownum-1,3), 2, ename ))
  5              , ',' 
  6              )
  7  from   emp
  8  group by deptno, floor( rownum/3-.1)
  9  order by deptno, floor( rownum/3-.1);

    DEPTNO RTRIM(MAX(DECODE(MOD(ROWNUM-1,3)
---------- --------------------------------
        10 CLARK,,KING
        10 ,MILLER
        20 SMITH
        20 JONES
        20 ,SCOTT
        20 ,ADAMS
        20 FORD
        30 ,ALLEN,WARD
        30 ,MARTIN,BLAKE
        30 TURNER,,JAMES

10 rows selected.

?

Regards,
Martijn
Tom Kyte
August 09, 2013 - 4:52 pm UTC

you'd need to use row_number() if you want to add something to the group by.
ops$tkyte%ORA11GR2> select deptno,
  2         rtrim(
  3         max( decode( mod(r-1,3), 0, ename ))||','||
  4         max( decode( mod(r-1,3), 1, ename ))||','||
  5         max( decode( mod(r-1,3), 2, ename )), ',' )
  6    from (select deptno, ename,
  7                 row_number() over (partition by deptno order by ename, rowid) r
  8                from scott.emp)
  9   group by deptno, floor( r/3-.1)
 10   order by deptno, floor( r/3-.1)
 11  /

    DEPTNO RTRIM(MAX(DECODE(MOD(R-1,3),0,EN
---------- --------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES
        20 SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES
        30 MARTIN,TURNER,WARD



we need to "reset" the rownum at the break of each deptno - row_number() can do that for us.

Finally got it

Hoek, August 09, 2013 - 8:27 pm UTC

Why is it every time that once you *understand* how things work, it suddenly all becomes so simple in an instance...I was close, though, and learned other things as well ;)

Thanks a lot!





Tom Kyte
August 12, 2013 - 1:42 pm UTC

:)

using lead()

Ranjith, August 12, 2013 - 5:43 am UTC


select rtrim(n1||','||n2||','||n3,',') n from
(
select mod(row_number() over (order by empno),3) r,
ename n1,
lead(ename,1) over (order by empno) n2,
lead(ename,2) over (order by empno) n3
from emp
) where r = 1
/


"Expensiveness" of trim operations?

Kim Berg Hansen, August 12, 2013 - 7:22 am UTC

Hi, Tom

After noticing your method of concatenating strings with separators and then using RTRIM to remove surplus separator, I was reminded of a recent SQL quiz I created for the PL/SQL challenge.

http://plsqlchallenge.com/pls/apex/f?p=10000:659:::NO:659:P659_COMP_EVENT_ID,P659_QUESTION_ID,P659_QUIZ_ID:172445,8013,&cs=17AC72926557307C732CD7585EA81EDAB

In this quiz I recommend a solution with a similar method, but one of the quiz reviewers (Michael Brunstedt) raised an issue with it, because he remembered having talked to you where you allegedly said that trim operations were "expensive little buggers" (his words ;-) and so he questioned my recommendation.

The setup was:

create table plch_menu (
   dish_name   varchar2(20)
 , vegetarian  varchar2(1)
 , biodynamic  varchar2(1)
)
/

insert into plch_menu values ('Fish and chips','N','N')
/
insert into plch_menu values ('Mushroom stew' ,'Y','N')
/
insert into plch_menu values ('Carrot soup'   ,'Y','Y')
/
insert into plch_menu values ('Angus steak'   ,'N','Y')
/
commit
/


And the quiz asked for a SQL to make this output:

DISH_NAME            NOTES
-------------------- -------
Angus steak          BIO
Carrot soup          VEG+BIO
Fish and chips
Mushroom stew        VEG


Of several correct solutions my recommendation was:

select dish_name
     , rtrim(
          case vegetarian
             when 'Y' then 'VEG+'
          end ||
          case biodynamic
             when 'Y' then 'BIO+'
          end
        , '+'
       ) notes
  from plch_menu
 order by dish_name
/


Primarily because it easily expands like for example:

alter table plch_menu add (
   nuts        varchar2(1)
 , gluten      varchar2(1)
)
/

update plch_menu
   set nuts   = case dish_name
                   when 'Mushroom stew' then 'Y'
                   else 'N'
                end
     , gluten = case dish_name
                   when 'Fish and chips' then 'Y'
                   when 'Carrot soup' then 'Y'
                   else 'N'
                end
/
commit
/

select dish_name
     , rtrim(
          case vegetarian when 'Y' then 'VEG+' end ||
          case biodynamic when 'Y' then 'BIO+' end ||
          case nuts       when 'Y' then 'NUT+' end ||
          case gluten     when 'Y' then 'GLU+' end
        , '+'
       ) notes
  from plch_menu
 order by dish_name
/


But as I know in my case (unlike your case in this thread) that I will always trim exactly one character, it could also be written like:

select dish_name
     , substr(notes,1,length(notes)-1) notes
  from (
   select dish_name
        , case vegetarian when 'Y' then 'VEG+' end ||
          case biodynamic when 'Y' then 'BIO+' end ||
          case nuts       when 'Y' then 'NUT+' end ||
          case gluten     when 'Y' then 'GLU+' end notes
     from plch_menu
  )
 order by dish_name
/


For your case RTRIM is indicated, because you do not know whether 0, 1 or 2 separators are to be trimmed away. But for my case I can see that RTRIM will unnecessarily test the last character if it is '+', remove it, test the second-to-last character, and then stop.

On the other hand it might be very tiny overhead to use RTRIM and the inline view does make the alternative a bit more "cumbersome" ;-)

Would you say that the reviewer has a point and the SUBSTR method is recommended and RTRIM is "overkill" for cases where you know it will always trim away exactly one character? Is RTRIM really that "expensive"?

Thanks :-)
Tom Kyte
August 12, 2013 - 4:02 pm UTC

I do not recall ever saying trim functions where expensive.

(always ask for the statement in context, in writing. I've written more than I've said out loud by now I think! In context - it might have an entirely different meaning)


I recall many times saying "regexp" is a cpu hog - and if I can I replace regex with instr/substr/replace/trim.



ops$tkyte%ORA11GR2> create table t
  2  as
  3  select object_name || ',,' oname, a.*
  4    from all_objects a
  5  /

Table created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> @trace
ops$tkyte%ORA11GR2> alter session set events '10046 trace name context forever, level 12';

Session altered.

ops$tkyte%ORA11GR2> begin
  2          for i in 1 .. 10
  3          loop
  4                  for x in ( select oname from t ) loop null; end loop;
  5                  for x in ( select rtrim(oname,',') from t ) loop null; end loop;
  6                  for x in ( select object_name from t ) loop null; end loop;
  7                  for x in ( select rtrim(object_name,',') from t ) loop null; end loop;
  8          end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.



SELECT ONAME FROM T

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute     10      0.00       0.00          0          0          0           0
Fetch     7300      0.37       0.36       1014      20290          0      729270
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     7311      0.37       0.36       1014      20291          0      729270
********************************************************************************
SELECT RTRIM(ONAME,',') FROM T

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute     10      0.00       0.00          0          0          0           0
Fetch     7300      0.37       0.36          0      20290          0      729270
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     7311      0.37       0.37          0      20291          0      729270
********************************************************************************
SELECT OBJECT_NAME FROM T

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute     10      0.00       0.00          0          0          0           0
Fetch     7300      0.35       0.36          0      20290          0      729270
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     7311      0.36       0.36          0      20291          0      729270
********************************************************************************
SELECT RTRIM(OBJECT_NAME,',') FROM T

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute     10      0.00       0.00          0          0          0           0
Fetch     7300      0.39       0.41          0      20290          0      729270
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     7311      0.39       0.41          0      20291          0      729270



the builtin string functions are pretty much what I call "brutally efficient"

don't use them unless you have to of course, but by all means - use them.

Thanks, Tom

Kim Berg Hansen, August 13, 2013 - 6:31 am UTC

Thanks, Tom

Unfortunately Mike couldn't remember where he had heard you say it or in what context and I couldn't find anything in writing that could confirm his statement.

Thank you for debunking that before it turned into a myth ;-)

/Kim

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.