[thelist] Oracle "in" question & owed tip

Jerry Scannell JerryScannell at cox.net
Tue Nov 5 07:17:00 CST 2002


It has been a while since I used Oracle, but I do recall a few things about
this:

There are some select statements that dramatically degrade system
performance.  One of them is the FLD_NAME LIKE %text% construct which has to
read all characters of a string looking for text that is found anywhere
within the field.  (conversely, FLD_NAME LIKE text% isn't that bad because
it only has to compare the first part of the fields).   I believe the
FLD_NAME IN ( "data", "data2" ) also slows things down because it is very
similar to the LIKE %...% construct.

Jerry

----- Original Message -----
From: "Luther, Ron" <Ron.Luther at hp.com>
To: <thelist at lists.evolt.org>
Sent: Tuesday, November 05, 2002 7:55 AM
Subject: [thelist] Oracle "in" question & owed tip


> Hi Gang,
>
>
> I have an ASP page where I have a query selecting data
> from 7 different Oracle tables.
>
> It's running fine; I'm getting 'correct' data and the
> performance is acceptable.
>
> Now I need to change one of the where conditions from
> "Account_ID = '1234567890'" to
> "Account_ID IN ('1234567890', '2345678901')".
>
> It's killing the performance.
>
> * Is that normal?
> * Is there an alternative syntax that's known to have
> better performance?
>
> Thanks,
>
>
> <tip type="using Oracle Hints and Optimizer with Brio" author="Ronl">
>
> You can utilize Oracle hints and optimizer instructions with Brio.
>
> [You can easily use them in ASP and other server-side languages,
> but Brio is a gui report builder application so it might not be
> as obvious that you can still use them.]
>
> You'll need to add them as "computed items" to the Request Line.
>
> You'll also need to drag them to the front of the line ... since
> they have to be the FIRST selected item in order to be invoked.
>
> <example>
> Here is an example of the syntax to invoke the rule based optimizer:
> computed item = "/*+rule*/1"
>
> The generated SQL should look like "SELECT /*+rule*/1, field1, ....".
> </example>
>
> I hope that helps speed things up for you.
>
> </tip>
>
>
> RonL.
>
> --
> For unsubscribe and other options, including
> the Tip Harvester and archive of thelist go to:
> http://lists.evolt.org Workers of the Web, evolt !
>




More information about the thelist mailing list