Trouble with multi-table query

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 Received on Mon Nov 05 2001 - 09:27:35 CST