Instead of OR use UNION:

In a WHERE clause it is always advisable to use UNION in place of OR verb because using OR on a indexed column will perform a full-table scan instead of indexed retrieval by the oracle optimizer.
So if the column is not indexed then use OR verb in a WHERE condition or clause and if the column is an indexed then using UNION or IN will be much more efficient rather than OR verb.

Examples by using IN, OR and UNION:

Less efficient by using OR verb in WHERE clause:

SQL>SELECT *
 FROM hz_locations
 WHERE state = 'CA' OR state = 'UK' OR state = 'WA' OR state = 'MX';

More efficient by using IN:

SQL>EXPLAIN PLAN FOR
SELECT *
 FROM hz_locations
 WHERE state IN ('CA', 'UK', 'WA', 'MX');

Example by using OR vs UNION :

SQL>EXPLAIN PLAN FOR
SELECT state,location_id,city, postal_code FROM hz_locations WHERE
 state = 'CA' OR
 location_id = 600000324;

SQL>EXPLAIN PLAN FOR
 SELECT state,location_id,city, postal_code FROM hz_locations WHERE
 state = 'CA'
 UNION
 SELECT state,location_id,city, postal_code FROM hz_locations WHERE
 location_id = 600000324;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s