Re: Help - Query running slow

From: Michael Sun <mikeny31_at_speakeasy.org>
Date: Sat, 6 May 2000 20:46:22 -0400
Message-Id: <10489.105134@fatcity.com>

This is a multi-part message in MIME format.

------=_NextPart_000_0081_01BFB79C.2346FFE0 Content-Type: text/plain;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Before you *optimize* this query, I suggest you to heed Rajagopal's = advice and make it *perform* first.=20

The biggest problem is of course the SELECT * FROM * INTO * statement. = It will trigger either NO DATA FOUND or TOO MANY ROWS exception if = either situation arises, and compounded by lack of exception handling = block in your code, that will be then end of your code execution right = there. Plus, I noticed that nobody has pointed out SELECT * INTO * = statement itself is a performance hitter, since it will inevitably do an = additional fetch to make sure you can only find one row of record meet = your query cirteria.

So to sum it up, here is the my list of suggestions:

using an explicit cursor instead of SELECT .. INTO .. statement
If you are using Oracle8i, and you have to use RTRIM function, use = function-based index (FBI) on that column. You will need to set the = following two parameters in your init.ora file

query_rewrite_enabled               TRUE                         =20
query_rewrite_integrity              TRUSTED =20

and you will need to grant 'query rewrite' privilege to the user. Then = you simply do this:

create unique index index_name on emp_code (rtrim(mgmt_id));

Then verify using explain plan that your current query is using this = newly created index.

Michael

------=_NextPart_000_0081_01BFB79C.2346FFE0 Content-Type: text/html;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Diso-8859-1" =
http-equiv=3DContent-Type>
<META content=3D"MSHTML 5.00.2919.6307" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV><FONT face=3DArial size=3D2>Before you *optimize* this query, I =
suggest you to=20
heed Rajagopal's advice and make it *perform* first. </FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>The biggest problem is of course the =
SELECT * FROM=20
* INTO * statement. It will trigger either NO DATA FOUND or TOO MANY = ROWS=20
exception if either situation arises, and compounded by lack of = exception=20
handling block in your code, that will be then end of your code = execution right=20
there. Plus, I noticed that nobody has pointed out SELECT * INTO * = statement=20
itself is a performance hitter, since it will inevitably do an = additional fetch=20
to make sure you can only find one row of record meet your query=20 cirteria.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>So to sum it up, here is the my list of =

suggestions:</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>1) using an explicit cursor instead of =
SELECT ..=20
INTO .. statement</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>2) If you are using Oracle8i, and you =
have to use=20
RTRIM function, use function-based index (FBI) on that column. You will = need to=20
set the following two parameters in your init.ora file</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial=20

size=3D2>query_rewrite_enabled       &= nbsp;      =20 TRUE           &nb= sp;           &nbs= p; =20
<BR>query_rewrite_integrity       &nbs=
p;     =20
TRUSTED  </FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>and you will need to grant 'query =
rewrite'=20
privilege to the user. Then you simply do this:</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>   create unique index =
index_name on=20
emp_code (rtrim(mgmt_id));</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>Then verify using explain plan that =
your current=20
query is using this newly created index.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>Michael</FONT></DIV>
Received on Sat May 06 2000 - 19:46:22 CDT