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 :-)
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.