Re: Trouble with multi-table query

ebowman_at_systec.com (Eric Bowman) wrote in message news:<3a1c1b63.0111050727.7947a233_at_posting.google.com>...
> I am having trouble getting a query worked out for a particular
> application. Here is the situation:
>
> The user needs to find an address based on several fields (street
> name, number, town, business name, etc.). They may enter full,
> partial, or no information in any of the fields. The software has to
> display any addresses that match or partially match the user input.
> The primary requirement of this application is to return results very
> quickly. The application is a vb front end and pads the user input
> with wildcards `%` where necessary.
>
> When I query the address table, it works fine. I run into trouble
> when I bring additional tables into the select.
>
> There is a basic address table (LOC_ADDRESS), which has all fields
> (loc_id, loc_type, street_name, street_type, street_num,
> loc_description). Primary key is loc_id, loc_type.
>
> There is a LOC_AKA_ADD table that has fields loc_id, loc_type, aka_id,
> aka_street_name, aka_street_type, aka_street_num. Primary key is
> loc_id, loc_type, aka_id.
>
> There is a LOC_AKA_DESC table that has fields loc_id, loc_type,
> aka_id, aka_description. Primary key is loc_id, loc_type, aka_id.
>
> The aka tables exist solely for this searching function. For example
> if the user types `E. Broadway` as their criteria and the address is
> in the main table as `Broadway E.`, there will be an aka listing that
> will match their input. There will also be aka entries for `e
> broadway`, `east broadway`, previous names if the street name has
> changed, etc. The list also includes business locations and
> intersections. A business or intersection address will have the name
> in the loc_description field; `Main st. & 5th Ave` for example. This
> intersection will have an aka record of `5th Ave & Main st` and
> possibly several others.
>
> I can execute a query on either of the three tables and the results
> are returned very quickly (fraction of a second). However, when I try
> a join like below, the performance drops considerably, taking up to 15
> seconds to return the same results:
>
> SELECT
> LOC_ID, LOC_TYPE
> FROM
> LOC_ADDRESS LA, LOC_AKA_ADD LAA, LOC_AKA_ DESC LAD
> WHERE
> LA.LOC_ID = LAA.LOC_ID(+)
> AND LA.LOC_TYPE = LAA.LOC_TYPE(+)
> AND LA.LOC_ID = LAD.LOC_ID(+)
> AND LA.LOC_TYPE = LAD.LOC_TYPE(+)
> AND (LA.STREET_NAME LIKE :ST_NAME OR
> LAA.AKA_STREET_NAME LIKE :ST_NAME)
> AND (LA.LOC_DESCRIPTION LIKE :DESC OR
> LAD.AKA_DESCRIPTION LIKE : DESC)
>
> The tables are not very large (approximately 90000 recs, 10000 recs,
> and 5000 recs, respectively) and all the fields that are used for
> joins or criteria are indexed.
>
> Any suggestions on how I could better set this up would be greatly
> appreciated. Thanks in advance.
>
> Eric Bowman
> ebowman_at_systec.com

Have you run a trace to see if the indexes are being hit? The syntax of your joins is most likely causing the optimizer to perform full table scans.

Without taking a ton of time to figure this out, I have a possible avenue you could approach. First, when doing joins, the Oracle optimizer is sensitive to the order in which the tables are presented in the FROM clause. Generally, the tables in which comparison is being made to input variables should come last in this clause and tables that join solely to other tables should come nearer the top of the list. So, if you want a query that will get a customer's name based on an address the query would look like

Select a.name, b.address
from names a, addresses b
where b.address like '%whatever%'
and a.cust_num=b.cust_num

Also, you might want to look into using hints to force the optimizer to use the indexes you want.

I don't have a ton of experience with how the LIKE clause will affect index selection. Another possibility is to use a UNION and sift the results in the VB app, or use separate calls and do the same thing.

Zman Received on Fri Nov 09 2001 - 10:53:13 CST