Salu
The way to avoid rtrim is to use LIKE and use % on the
variable. ( To use RPAD on the variable you must know
the no of spaces...in the table column !!)This will
enable the index. If you are using a char column then
listen to the advice of Carlos Alvarez !! use a
varchar column !!
> SELECT DISTINCT emp_id INTO ret_empid
> > FROM emp_code
> > WHERE MGMT_CD LIKE empid||'%';
As i read your initial question i also find another
problem. I think-think you are calling a PL/SQL
function from a sql select. That will slow down the
query any way. You should call SQL from PL/SQL. But
calling PL/SQL from SQL is not a wise idea.......( I
agree there are exceptions).
Using LIKE will solve the problem most probably...in
case if it doesn't ..
What you can do is instead of calling the function in
the initial select write a select like this before
that 'initial' select
cursor blabla is
SELECT emp_id , MGMT_CD, MRKT_CD, ACCT_CD
FROM emp_code
WHERE ( MGMT_CD LIKE empid||'%' or ACCT_CD
LIKE empid||'%' OR MRKT_CD LIKE empid||'%' )
.........
IF MGMT_CD IS NOT NULL THEN..........
variable_for_initial_select := emp_id;
THEN write the 'initial' SELECT with the variable
'variable_for_initial_select'
Alroy
mark_alroy_at_yahoo.co.uk
- "Alvarez, Carlos" <CAlvarez_at_uniFON.com.ar> wrote:
> 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
> > >
> >
>
>--------------------------------------------------------------------
Received on Sat May 06 2000 - 01:52:04 CDT
