Re: SQL server 7 equivalent code in Oracle?
Date: 2000/06/13
Message-ID: <8i54m9$2m64$1_at_adenine.netfront.net>
How can I speed up the performance of user-defined function?
In comp.databases.oracle.server Eugenio <eugenio.spadafora_nospam_at_compaq.com> wrote:
> I'm not sure, but I think that decode should be faster than function.
> I tried on my test server (not fine tuned, obviously) with this 2 scripts
> Script1
> select sysdate from dual
> /
> select max(f(price)) from t
> /
> select sysdate from dual
> /
> /* f is the function
> create or replace function f(v_price in number) return varchar2
> is
> v_eval varchar2(100) := 'error in evaluating';
> begin
> begin
> if v_price is null then
> v_eval := 'Not yet priced';
> elsif v_price < 10 then
> v_eval := 'Very Reasonable Title';
> elsif v_price < 200 then
> v_eval := 'Coffee Table Title';
> elsif v_price < 211 then
> v_eval := 'Tea Table Title';
> else
> v_eval := 'expensive book';
> end if;
> exception
> when others then null;
> end;
> return v_eval;
> end;
> */
> Script2
> select sysdate from dual
> /
> select
> max(decode(price,
> null, 'Not yet priced',
> decode(trunc(price/10),
> 0, 'Very Reasonable Title',
> decode(trunc(price/200),
> 0, 'Coffee Table Title',
> decode(trunc(price/211),
> 0, 'Tea Table Title',
> 'Expensive book!'
> )
> )
> )
> )
> )
> from t
> /
> select sysdate from dual
> /
> on a 390000 rows table t(price number)
> Script1 taked 22 seconds
> Script2 taked 6 seconds
> --
> be happy
> Eugenio
> remove _nospam from reply address
> Opinions are mine and do not necessarily reflect those of my company
> =======================================================
> Norris wrote in message <8i521r$2klr$1_at_adenine.netfront.net>...
>>From the performance point of view, which is faster: user defined stored
function or decode or cursor or temp table...?
>>
>>In comp.databases.oracle.server Klaus Zeuch <Klaus.Zeuch_at_erls04.siemens.de>
wrote:
>>> with a stored function like
>>> create or replace function fct_price_eval(v_price in number) return varchar2
>>> is >>> v_eval varchar2(100) := 'error in evaluating'; >>> begin >>> begin >>> if v_price is null then >>> v_eval := 'Not yet priced'; >>> elsif v_price < 10 then >>> v_eval := 'very reasonable title'; >>> -- further evaluation code with elsif.....then.....; >>> else >>> v_eval := 'expensive book'; >>> end if; >>> exception >>> when others then null; >>> end; >>> return v_eval; >>> end; >>> /
>>> you should be able to handle every logic and can avoid lengthy >>> decode-statements.
>>> SELECT price, fct_price_eval(price),... >>> FROM .... >>> regards
>>> Klaus >>> Norris <jcheong_at_cooper.com.hk> schrieb in im Newsbeitrag: >>> 8i4vsk$2jo2$1_at_adenine.netfront.net... >>>> Thanks for your suggestion. But what should I do if there is negativevalue in price column?
>>>> >>>> >>>> In comp.databases.oracle.server Eugenio<eugenio.spadafora_nospam_at_compaq.com> wrote: >>>> > Probably it could be found an easier way !! >>>> > The way is trunc division by higher edge of the range and compare with
0,
>>>> > otherwise...other range
>>>> > (obviously ordered by value ascending)
>>>> > select >>>> > decode(price, >>>> > null, 'Not yet priced', >>>> > decode(trunc(price/10), >>>> > 0, 'Very Reasonable Title', >>>> > decode(trunc(price/200), >>>> > 0, 'Coffee Table Title', >>>> > decode(trunc(price/211), >>>> > 0, 'Tea Table Title', >>>> > 'Expensive book!' >>>> > ) >>>> > ) >>>> > ) >>>> > ) >>>> > from titles >>>> >>>> >>>> > -- >>>> > be happy
>>>> > Eugenio
>>>> > remove _nospam from reply address
>>>> > Opinions are mine and do not necessarily reflect those of my company
>>>> > ======================================================= >>>> > Norris wrote in message <8i4s16$2hif$1_at_adenine.netfront.net>... >>>> >>How can I decode the following query? >>>> >> >>>> >>SELECT >>>> >>CASE >>>> >>WHEN price IS NULL THEN 'Not yet priced' >>>> >>WHEN price < 10 THEN 'Very Reasonable Title' >>>> >>WHEN price >= 10 and price < 200 THEN 'Coffee Table Title' >>>> >>WHEN price >= 200 and price < 211 THEN 'Tea Table Title' >>>> >>ELSE 'Expensive book!' >>>> >>END >>>> >>FROM titles
>>>> >>
>>>> >>In comp.databases.oracle.server Eugenio <eugenio.spadafora_nospam_at_compaq.com> wrote: >>>> >>> This should work for SP.
>>>> >>> SELECT d.departmentid, >>>> >>> decode(trunc(p.departmentID/100), >>>> >>> 0, 'Video', >>>> >>> 1, 'CD-ROM', >>>> >>> 2, 'Book', >>>> >>> 'DVD') as dept >>>> >>> from product p, department d >>>> >>> where d.prodID = p.prodID >>>> >>> order by dept desc >>>> >> >>>> >> >>>> >>> -- >>>> >>> be happy
>>>> >>> Eugenio
>>>> >>> remove _nospam from reply address
>>>> >>> Opinions are mine and do not necessarily reflect those of my company
>>>> >>> ======================================================= >>>> >>> Norris wrote in message <8i49j4$22gl$1_at_adenine.netfront.net>... >>>> >>>>Which version of Oracle are you using? >>>> >>>>
>>>> >>>>In comp.databases.oracle.server Mary <mdellamalva_at_cetec.com> wrote: >>>> >>>>> Hello SP,
>>>> >>>>> You want to use DECODE:
>>>> >>>>> SELECT d.departmentid,
>>>> >>>>> >>>> >>> >>>> > >>>
dept=DECODE(p.departmentID,p.departmentID<100,'Video',DECODE(p.departmentID,
>>>> >>>>> p.departmentID<200 >>>> >>>>> ,'CD-ROM',DECODE(p.departmentID,p.departmentID<300,'Book','DVD'))) >>>> >>>>> from product p, department d
>>>> >>>>> In a Nutshell it is: decode(<CASE>,<WHEN>,<THEN>,<ELSE>)
>>>> >>>>> Hope this helps
>>>> >>>>> Mary
>>>> >>>>> mdellamalva_at_cetec.com
>>>> >>>>> SP <spant1_at_aol.com> wrote in message >>>> >>>>> news:MVe15.1169$Qf6.46762_at_nuq-read.news.verio.net... >>>> >>>>>> I've this SQL code inside a PL/SQL body. But this is in SQLserver
7.
Can
>>>> >>>>>> anyone suggest eqivalent code in Oracle. >>>> >>>>>> SELECT d.departmentid, >>>> >>>>>> 'dept'=CASE >>>> >>>>>> WHEN p.departmentID<100 >>>> >>>>>> THEN 'Video' >>>> >>>>>> WHEN p.departmentID<200 >>>> >>>>>> THEN 'CD-ROM' >>>> >>>>>> WHEN p.departmentID<300 >>>> >>>>>> THEN 'Book' >>>> >>>>>> ELSE 'DVD' >>>> >>>>>> END >>>> >>>>>> from product p, department d >>>> >>>>>> where d.prodID = p.prodID >>>> >>>>>> order by dept desc >>>> >>>>>> >>>> >>>>>> Thanks, >>>> >>>>>> SP >>>> >>>>>> >>>> >>>>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>>-- >>>> >>>>http://www.cooper.com.hk >>>> >> >>>> >> >>>> >> >>>> >> >>>> >>-- >>>> >>http://www.cooper.com.hk >>>> >>>> >>>> >>>> >>>> -- >>>> http://www.cooper.com.hk >> >> >> >> >>-- >>http://www.cooper.com.hk
-- http://www.cooper.com.hkReceived on Tue Jun 13 2000 - 00:00:00 CEST