September 23, 2011 Leave a comment
So as it turns out, the SQL OR clause is pretty devious when it comes to index usage in Oracle.
Consider a table with two columns, lets call them A and B – both indexed to allow null values
CREATE INDEX MY_SCHEMA.IX_1 ON MY_SCHEMA.MY_TABLE (A, 1) TABLESPACE USERS; CREATE INDEX MY_SCHEMA.IX_2 ON MY_SCHEMA.MY_TABLE (B, 1) TABLESPACE USERS;
Now consider the following queries
select * from MY_TABLE where A='a' or (A is null and B='b')
select * from MY_TABLE where A='a' union select * from MY_TABLE where A is null and B='b'
While logically identical, intuitively you would think that the first one is better from a performance viewpoint. Only one select, right? As it turns out, the second query is a LOT cheaper. In a similar real-world scenario I recently worked on optimizing, using an OR clause compared to a union resulted in an execution-plan with a calculated cost 10 times higher. Why?
The OR clause doesn’t always utilize indexes the way you would expect. In this case, the OR clause forced a full table-scan while the union could be executed by two index-scans.
So the lesson I learned is that when you are down and working to optimize your database operations, the presence of an OR clause should raise a red flag. It’s worth attempting to re-write the query as a union instead and indexing based on each separate union-query. Chances are, all of a sudden your queries will start hitting your indexes instead of causing full table-scans.