The dreaded OR statment (SQL)

If you’ve ever written a lot of SQL, and since taking the job I’m currently at, I spend 90% of every day writing SQL for Oracle databases. One thing I have come across is the OR statement is usually the worst the thing that can happen to your query.

Now, I do not fully understand how exactly this works behind the scenes, it is something I need to further investigate, but one would logically assume that

select *
from table_foo
where bar in('A','B')

would be equivlent to

select *
from table_foo
where bar = 'A'
or bar = 'B'

I can tell you from experience while the above example is contrived, once your query starts touching tables in large quantities the difference becomes obvious. For whatever reason this is not the case. I suppose since you’re working with a query language there is no compiler to optimize your code for you, though I wish there was. Or, if by chance, there is, it is apparently not smart enough to make the conversion for you.

Moving on, it is also possible that if using an in() statement is not an option you can also replace it with a UNION. This situation would only arise though in very limited situations, but it is still possible. For example, say we have table_foo setup like so
p_key    f_key    alpha_1
1              2              A
2              4              B
3              5              C
4              6              D

So instead of doing

select *
from table_foo foo, table_bar bar
where foo.p_key = bar.fake_key
or foo.f_key = bar.fake_key

you can instead use a UNION

select *
from table_foo foo, table_bar bar
where foo.p_key = bar.fake_key
UNION
select *
from table_foo foo, table_bar bar
where foo.f_key = bar.fake_key

by arranging you code in this way you make sure that the database is making good use of any indexes that have been created on the table. This might be a bit to difficult to apply in the real world, but if you can it will be amazing how much faster the UNION will return results as opposed to the or above it.

Enjoy the Penguins!

About these ads