SQL Tuning - IN/UNION versus OR

SQL Tuning - IN/UNION versus OR -  In general, always consider the IN and/or UNION verb instead of the OR verb in WHERE clauses. Using the OR predicate on an indexed column causes the optimizer to perform a full table scan rather than an indexed retrieval.

Note:  Choosing IN or UNION over OR will be effective only if the column(s) is indexed. If the column(s) is not indexed, you may actually increase overhead by not choosing OR.

In the following examples, both loc_id and region are indexed. Consider the following:

   SELECT . . .                     SELECT . . .
   FROM   location                  FROM   location
   WHERE  loc_id = 10               WHERE  loc_id = 10
   OR     region = 'MELBOURNE'      OR     loc_id = 20
                                    OR     loc_id = 30

To improve performance, replace this code with:

   SELECT . . .                     SELECT . . .
   FROM   location                  FROM   location
   WHERE  loc_id = 10               WHERE  loc_in IN (10,20,30)
   UNION
   SELECT . . .
   FROM   location
   WHERE  region = 'MELBOURNE'

If you do use OR, be sure that you put the most specific indexed clause first in the OR's predicate list and put the index that passes the most records last in the list.

Note that the following:

WHERE key1 = 10         Should return least rows
OR    key2 = 20         Should return most rows

is internally translated to

WHERE key1 = 10
AND ( key1 NOT = 10 AND key2 = 20 )