RE: Help - Query running slow

BUT BE CAUTIOUS ABOUT USING % AS IT MIGHT RETURN A WRONG VALUE(S) ALSO !!!

• Alroy Mascrenghe <mark_alroy_at_yahoo.co.uk> wrote:
  > 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
  >
  === message truncated ===