with the rtrim function, you cannot use index on those
selects. Try use 'like'.
- salu Ullah <salu_ullah_at_hotmail.com> wrote:
> Hello,
>
> I'am getting a very slow response on the query that
> calls a function after
> getting a variable.
> The query is used in pert script.
> The function after getting the variable do a select
> statement & return a
> value to the query. The response time using the
> fuction in a query is very slow. If i type the value
> & run the same query it
> runs very fast.
> Almost twice as fast as running with a function.
> Cant figure out what's causing it slow...is it the
> rtrim iam using?????
> I also created an index on these columns but no
> improvement.
>
> Any input will be appreciated
>
> Below is the funtion iam using:
>
> create or replace funtion e_conv(empid VARCHAR2,
> name varchar2)
> RETURN VARCHAR2 IS
> ret_empid VARCHAR2(6);
> BEGIN
> if name = 'MGMT' then
> SELECT DISTINCT emp_id INTO ret_empid
> FROM emp_code
> WHERE rtrim(MGMT_CD) = empid;
>
> elsif name = 'ACCT' then
> SELECT DISTINCT emp_id INTO ret_empid
> FROM emp_code
> WHERE rtrim(ACCT_CD) = empid;
>
> elsif name = 'MRKT' then
> SELECT DISTINCT emp_id INTO ret_empid
> FROM emp_code
> WHERE rtrim(MRKT_CD) = empid;
>
> end if;
> RETURN ret_empid;
> END;
>
>
> Salman
>
> Get Your Private, Free E-mail from MSN Hotmail at
> http://www.hotmail.com
>
> --
> Author: salu Ullah
> INET: salu_ullah_at_hotmail.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
>
Do You Yahoo!?
Send instant messages & get email alerts with Yahoo! Messenger.
Received on Thu May 04 2000 - 20:02:23 CDT