Can Exadata take you Beyond “Infinity and Beyond” with Non-Numbers?

Let me clarify… in a previous post, I showed some of the arithmetic around some of Oracle’s floating point literals and how that arithmetic didn’t make much sense. But one of the other literals available helped me solve an issue with the optimizer, indexes, and Exadata: an anomaly with a smart phone app gave me an idea…

LEFT JOINs, NULLs, and Offloading on Exadata

I was investigating query performance, and the root cause appeared to be a join condition that also had a filter:

. . .
from table1
   left join table2
      on table1.id = table2.id
         and table2.cust_code IS NULL

Long story short, the join condition was rewritten without IS NULL like this:

   and nvl(table2.cust_code,-999) = -999

The problem is, there was no easy way to ensure that that column might not actually contain the value -999 short of adding a CHECK constraint, and it would have taken weeks to get a change like that into production.

Alternate Solution: A Number that’s Not a Number

Instead of using a constant that might actually be valid data, why not use one of Oracle’s built-in constants like BINARY_DOUBLE_NAN? The chances of the CUST_CODE column containing the value BINARY_DOUBLE_NAN are essentially zero because the UI for TABLE2 would not allow anything but numeric constants.

I tried out this method on a copy of DBA_TAB_COLS. The column DATA_PRECISION has a lot of NULL values; if I use NVL and the Oracle BINARY_DOUBLE_NAN constant to avoid the IS NULL construct, it works great:

select count(*) 
from col_data
where nvl(data_precision,BINARY_DOUBLE_NAN) = BINARY_DOUBLE_NAN;

  COUNT(*)
----------
    797961

I get the same number of rows as if I had used the IS NULL construct.

What About Exadata Offloading?

The question I always ask is, “how does this work on Exadata?” The answer did not disappoint.

Of course, single-row functions such as NVL are in V$SQLFN_METADATA and marked with OFFLOADABLE=YES; constants such as BINARY_DOUBLE_NAN are treated as any other numeric value.

Conclusions

Using Oracle’s built-in functionality including numeric constants representing very large numbers, very small numbers, and numbers that are actually not numbers can help you out of trouble with NULL values (which are also not numbers!). And Exadata will not get in your way using solutions like this either!