Re: Trouble with multi-table query

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:<tuo8ove4sgfgcf_at_corp.supernews.com>...
> "Zman" <mz_161_at_yahoo.com> wrote in message
> news:39789f2d.0111090853.9e74121_at_posting.google.com...
> > 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
>
>
> Your explanation applies basically to the Rule Based Optimizer only.
> RBO is _dead_. It hasn't been changed since 7.1
> Please do not recommend using it to anyone, they will be in a dead ally.
>
> In CBO the order of tables is just the other way around.
>
> Regards

Thanks for the correction--sometimes I get lost in my own little world. However, I wouldn't necessarily assume that rule based optimization is dead. Perhaps you should take a poll. We use it and have no intention of going cost-based. Perhaps we've our heads in the sand, but every time we've tried cost based, it's caused us more headaches than it's worth.

Zman Received on Mon Nov 12 2001 - 08:10:20 CST