Helen,

> >Here is an example to help clarify:
> >
> >SELECT *
> >FROM NAME_ADDRESS N
> >LEFT JOIN BILLING_DATA B
> > ON B.CO = N.CO
> > AND B.YR = N.YR
> > AND B.PARCEL = N.PARCEL
> >ORDER BY N.CO, N.YR, N.PARCEL
> >
> >Here is the resulting plan:
> >PLAN SORT (JOIN (N NATURAL,B INDEX (BILLING_DATA_00)))
>
> Specify it without "LEFT" and look at the plan then.

Without the LEFT it is very fast, however I only get records contained in
both tables. What I need is all records in N even if there is not a match in
B (like the join in the WHERE clause produces).

> Also, although some say it makes no difference to joins, try turning the
> match criteria around so that the sort fields are what the joined tables
> fields are being compared with, i.e.
>
> SELECT *
> FROM NAME_ADDRESS N
> JOIN BILLING_DATA B
> ON N.CO = B.CO
> AND N.YR = B.YR
> AND N.PARCEL = B.PARCEL
> ORDER BY N.CO, N.YR, N.PARCEL

I tried that and it did not appear to make a difference. Now that I know
what JoinLinks are used for, I can live with the join criteria in the WHERE
clause.

> fwiw, you could eliminate the ambiguities confronting the optimizer if you
> normalized on that CO,YR,PARCEL key structure. If it is that prevalent
> throughout the database, it really ought to be in its own table with a
> surrogate key which the other tables use as a link entity. Still, maybe
> you don't want to go there...

I am not sure that I understand you. The CO, YR and PARCEL fields are
basically the primary keys for each table. At login the user selects a
community and a year (i.e. CO and YR). Once logged in, the user works with
parcel related information for the logged in community and year. If I moved
CO, YR and PARCEL to a separate table and use a surrogate key instead, would
all my select statements then become even more complex joins ?

Example:

SELECT KY
FROM KEYS K
LEFT JOIN NAME_ADDRESS N
ON K.KY = N.KY
LEFT JOIN BILLING_DATA B
ON K.KY = B.KY
WHERE K.CO = '241'
AND K.YR = 2001
ORDER BY K.CO, K.YR, K.PARCEL

I am a firm believer in a normalized database. However, I typically do not
normalize the primary keys. Would the above example really be more efficient
for the optimizer ?

This sounds like a very interesting idea and would certainly reduce the
overall size of my database. In most cases I need the data in PARCEL order
for the logged in community & year and I just want to be sure that the
additional joins are not going to put even more of a burden on the server.

Thanks,

Chris Landowski
Dynamic Software Solutions