Mike wrote:
> On Jul 11, 10:35 am, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
>
>>Mike wrote:
>>
>>>tables:
>>>1. publication(pubid, title)
>>>2. book(pubid, date)
>>>3. journal(pubid, date)
>>
>>>purpose: find all publication titles for books or journals.
>>
>>>precondition: both book and journal tables are not empty. (if either
>>>is empty, the following solution 2 will be wrong. See my old post
>>>"difference between UNION operator and OR in where clause Options" for
>>>details).
>>
>>>Solution 1: use set "union"
>>
>>>select title
>>>from publication, ( (select pubid from book) UNION (select pubid from
>>>journal)) bj
>>>where publication.pubid = bj.pubid
>>
>>>solution 2: use "or" operator in the where clause
>>
>>>select DISTINCE title
>>>from publication, book, journal
>>>where publication.pubid = book.pubid or publication.pubid =
>>>journal.pubid
>>
>>>Both solutions are correct under the precondition.
>>
>>>My question is which one should use for better performance ?
>>
>>>My intuitive guess will be solution 2.
>>
>>>Any comments ?
>>
>>As Roy pointed out, intuition is a really bad guide. While I expect
>>things have since changed drastically, I can say with great confidence
>>that circa 1994, your intuition would have been wrong.- Hide quoted text -
>>
>>- Show quoted text -
>
> Fari enough. Do you mind sharing any reasons why it would have been
> wrong?
It was wrong by simple empirical measurement. I can point out the
problems with NULL and duplicates complicating the algebra. Complicating
the algebra complicates the optimizer and delays otherwise obvious
optimizations until someone works out the complications and when they do
not apply.
However, whether those complications fully explain the empirical measure
is something I truly do not know.
Received on Wed Jul 11 2007 - 10:01:12 CDT
