Use DECODE instead of IN for performance
                                                 Last update (2008-02-19 20:53:41)
                                                                                                                   Date added (2008-02-19 20:51:40)

Summary
If you've filtered with an IN (..,..) you've noticed some heavy performance hits. If you look at the explain plan, you'll find that it is producing table scans for each of the elements in the IN list. There's a logically equivalent method using a DECODE() function that reduces it to a single scan. If you have a filter that looks like:
where state_code in ('TX','OK','LA','NM');
You can replace it with:
where decode(state_code,'TX',1,'OK',1,'LA',1,'NM',1,0) = 1;
and significantly reduce your table scans. (Note the 0 as the default in the decode results.)
Reviews
Filters
Search