Re: Help - Query running slow

From: Rajagopal Venkataramany <rajagopalvr_at_hotmail.com>
Date: Fri, 05 May 2000 01:18:02 GMT
Message-Id: <10487.105005@fatcity.com>

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----
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: Help - Query running slow
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).



________________________________________________________________________

Received on Thu May 04 2000 - 20:18:02 CDT