RE: Help - Query running slow

Yes!. the make_sql_run_faster parameter is the best solution when you use function on index columns!

another good solution is not to use function on index columns. almost every function has its inverse. Try this in the parameter (ie, try using rpad in the variable instead of rtrim in the column)

if the table is using char columns, then the column will be filled with blanks. (use varchar to avoid rtrim).

Look

CREATE TABLE a ( a CHAR(10))

INSERT INTO a VALUES ('a')

SELECT LENGTH(RTRIM(a)) FROM a
1

SELECT LENGTH(a) FROM a
10

> ----------
> De: Joseph S. Testa[SMTP:teci_at_oracle-dba.com]
> Responder a: ORACLE-L_at_fatcity.com
> Enviado el: Viernes 5 de Mayo de 2000 19:26
> Para: Multiple recipients of list ORACLE-L
> Asunto: 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
> > (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: Joseph S. Testa
> INET: teci_at_oracle-dba.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
Received on Fri May 05 2000 - 16:55:21 CDT