Re: performance difference between UNION operator and OR in where clause Options

"Mike" <gongweigang_at_gmail.com> wrote in message news:1184165373.980289.256250_at_d55g2000hsg.googlegroups.com...

> Since I am new to database programming, what I would like to see is
> any guide lines (or lessons, or experience) when picking between a
> "UNION" operator and an "OR" operator. For example, suppose you face
> such situation, which one you will choose ?

>
> My experience told me that a good programmer will always try to use a
> better algorithm or solution, instead of relying on the optimization
> during runtime.
>

Roy Hann has already outlined why this is generally the wrong approach to getting the most out of a well built DBMS, a well designed database, and the power of the relational model. In general, you should focus on WHAT the query is going to return to you, and (wherever possible) delegate the HOW (choice of retrieval strategy) to the RDBMS and its optimizer. In addition to what's already been said, I'm going to point out that the run time optimizer has the cardinalities of the various tables available to it, where as optimization at coding time has to rely on estimates.

You have already pointed out that solution 2 is less robust than solution 1 in that certain empty table conditions will cause solution 2 to yield wrong results. This alone would be reason enough to pick solution 1 in many real life situations. The cost of wrong answers can be quite a bit higher than the cost of higher delays.

Database work is different from programming. I learned this when I started out in databases after 20 years as a programmer. If you are like most programmers (including me when I first cut over) you will probably reject this wisdom, until you learn it from your own experience. Received on Wed Jul 11 2007 - 19:25:04 CEST