Re: Help - Query running slow

did u try the _make_sql_run_faster undocumented init.ora parm?

what version of the database you on, since it works different on each version.

8.0 = _make_sql_run_faster = true
8.1 = _make_sql_run_faster= #  where # is a number from 1 to 9
8.2 = _make_sql_run_faster=unlimited  absolute speed.

<SHAMELESS PLUG COMING UP>

everyone have a good week next week, for those of you going to IOUG-A, come and see me at my logminer presentation on mon from 3:15 - 4:15

the above init.ora joke was brought to you by:

Joe, the not so kind or gentle DBA

salu Ullah wrote:

> 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).
>
> ________________________________________________________________________
> 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
Received on Fri May 05 2000 - 14:47:29 CDT