Re: Trouble with multi-table query

"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

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address