| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Help - Query running slow
Salu,
Why do you need to use RTRIM in the WHERE condition ? What is the datatype of the columns?
My suggestion would be that using RTRIM looks like redundant to me.
Removing the RTRIM function itself should solve most of the issues.
**
Regards
Rajagopal Venkataramany
----Original Message Follows----
From: "salu Ullah" <salu_ullah_at_hotmail.com>
Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Subject: Re: Help - Query running slow
Date: Fri, 05 May 2000 07:40:01 -0800
Hello rajgopal,
How can i make it run fast, since indexing on a column using function is not giving a good response. Any other alternate that i can use in my query??
Thanks
Salman
>From: "Rajagopal Venkataramany" <rajagopalvr_at_hotmail.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: Re: Help - Query running slow
>Date: Thu, 04 May 2000 18:27:11 -0800
>
>Salu
>
> The slowness in the response time is because of the RTRIM function
> on MGMT_CD, ACCT_CD, and MRKT_CD (assuming u have an index on them).
>
> For your understanding, when you qualify any indexed column with
> any functions then the optimizer would ignore those indexed columns.
>
> Some Observations :
>
>1.
> Since you want distinct emp_id, this query MAY return more than
> one value if your WHERE condition encounters different emp_id.
> In such cases the query will fail with 'TOO MANY ROWS' error.
>
>2.
> Since there is no EXCEPTION handling, this function may abort incase
> it does not find any HIT.
>
>You may look into this.
>
>Thanks
>Rajagopal Venkataramany
>
>----Original Message Follows----
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Date: Thu, 04 May 2000 16:07:04 -0800
>
>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).
>
>________________________________________________________________________
>Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
>
>--
>Author: Rajagopal Venkataramany
> INET: rajagopalvr_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).
-- 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).Received on Fri May 05 2000 - 11:09:44 CDT
 |
 |