Re: Is this JOIN good ?

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 16 Oct 2006 18:23:01 -0700
Message-ID: <1161048176.41950@bubbleator.drizzle.com>

Andy Hassall wrote:

> On Mon, 16 Oct 2006 13:11:50 -0700, DA Morgan <damorgan_at_psoug.org> wrote:
> 
>> Charles Hooper wrote:

>>> klabu wrote:
>>>> Assuming you don't know anything about these 3 tables beyond FK & PK
>>>>
>>>> select *
>>>> from A, B, C
>>>> where A.fk = C.pk
>>>> and B.fk = C.pk
>>>>
>>>> Is this JOIN "complete" ?
>>>> or an additional predicate is needed between A and B ?
>>> Transitive closure:
>>> A = C
>>> B = C
>>> Logic states that this implies that:
>>> A = B
>>>
>>> Perform a 10053 trace, you may see the Cost Based optimizer adding a
>>> constraint like this:
>>> A.FK = B.FK

>> Which, if it were true, would indicate a bad design.
> 
>  In what way do you believe it indicate this?

If A.FK and B.FK are both lookups to tables holding the same set of values I'd say you have a design flaw.

Well that and the fact that you can not write a foreign key to two different tables from the same column.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Mon Oct 16 2006 - 20:23:01 CDT