Re: performance difference between UNION operator and OR in where clause Options
From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Wed, 11 Jul 2007 12:01:12 -0300
Message-ID: <4694f09b$0$8831$9a566e8b_at_news.aliant.net>
>>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 -
Date: Wed, 11 Jul 2007 12:01:12 -0300
Message-ID: <4694f09b$0$8831$9a566e8b_at_news.aliant.net>
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 - 17:01:12 CEST