Hi,
I saw situations like this a couple of times in just the last few days. My first reaction, both times, was "aha! good to know and use in the future." The second reaction, much later and upon reflection, was "isn't that sacrificing some efficiency though?" I'll present the issue and open it up for comments and suggestions. Trying to learn from this as much as I can.
Scenario 1.
I have a table, it has several columns, and many rows. The first column, col1, is a string column (let's say varchar2(1000) for clarity's sake). Now I want to select all rows where col1 matches a regular expression pattern (perhaps given as a parameter, or as a variable from an application, etc.) But, for whatever reason, I must ALSO pull all the rows where col1 is null.
Solution 1 A (using OR operator):
... WHERE REGEXP_LIKE(col1, 'match_pattern') OR col1 IS NULL
Solution 1 B (clever trick):
... WHERE REGEXP_LIKE(COALESCE(col1, 'zzzzz'), 'match_pattern|zzzzz') -- assume I made sure 'zzzzz' is not a possible value in col1
Scenario 2.
I have a table, it has several columns... Now I have two text columns, col1 and col2, and I want to select all the rows in which col1 = col2, but also all the columns in which both are null.
Solution 2 A (using OR operator):
... WHERE col1 = col2 OR (col1 IS NULL AND col2 IS NULL) -- parentheses not really needed, but they add clarity
Solution 2 B (clever trick, taking advantage of the fact that DECODE treats nulls as "equivalent", meaning they match):
... WHERE DECODE(col1, col2, 0) = 0 -- if the columns don't match, DECODE returns null and the row is not selected
In both scenarios, I know not to use UNION as a substitute for OR - I want the table to be scanned once, not twice.
So, here is what I think. In both cases solution B is elegant, it shows a good understanding of Oracle functions, and it treats null and non-null values on more-or-less equal footing, which has some appeal.
But I believe this comes at the cost of performance, at least to some degree. In both cases, there is a function call (to COALESCE, resp. DECODE), on ALL rows in the table. Also, when col1 is not null (in Scenario 1), resp. when col1 and col2 are not null (Scenario 2), the decision is made in the first part of Solution A; checking for null is avoided. Also, in Scenario 2, even when col1 and col2 are not null, instead of making a single comparison of col1 and col2, when they match, I make TWO comparisons: first of col1 and col2 to assign 0 to DECODE, and then again comparing 0 to 0.
So it seems that solution B is at least marginally more expensive than solution A in both cases. Does this make sense? Or am I full of it? My inclination, right now, is to use solutions like A whenever I can, knowing full well that there will be cases when a solution like A simply doesn't exist, or is much more complicated than the corresponding B.
Thank you, - mathguy-ro