When OR Is a Bad Thing
OR is a very powerful construct in databases. Unfortunately, it is one that optimizers often do not handle well, at least with respect to indexes.
Simple WHERE clauses with conditions on only a single column generally do not pose a problem. SQL optimizers are smart enough to use an index on Orders(State)for a query such as:
SELECT o.* FROM Orders o WHERE State = 'MA' OR State = 'NY'
Note that this would be better written with IN. The two constructs are usually identical with respect to optimization.
Sometimes UNION ALL Is Better Than OR
Consider an index on Orders(State, City) and a query to fetch orders from Boston and Miami:
SELECT o.*
FROM Orders o
WHERE (o.State = 'MA' AND o.City = 'BOSTON') OR
(o.State = 'FL' AND o.City = 'MIAMI')
SQL optimizers may not recognize the opportunity to use the index because of the OR. Even if the optimizer is smart enough in this case, it may miss the opportunity...