In “Case” you missed it: Part 1

And you probably didn’t miss it, since I just posted it. A question came up a couple times recently, “Do WHERE clauses with CASE expressions offload to storage on Exadata?” Short answer: yes, although it doesn’t seem intuitive.

Offloadable Functions on Exadata

On any Oracle database, even those not on Exadata, you can easily check to see if any built-in function is offloadable in the view V$SQLFN_METADATA. The built-in function DECODE is in there as OFFLOADABLE:

But what about CASE? It’s not really a function, but an expression (it’s a statement in PL/SQL), and it’s nowhere to be found in V$SQLFN_METADATA. It sure looks like a DECODE, though. So is it offloadable?

To find out if at least some CASE expressions can be offloaded, I created a table with over 51M rows — the predicate column being very selective for HHG_NUM=42 and likely a good candidate for Exadata storage offloading.

CASE in the WHERE Clause

Next, I ran two equivalent queries, the first written with a CASE expression in the WHERE clause:

select note_num, note_txt
from notes
where
   case
      when hhg_num = 40 then 'Life'
      when hhg_num = 41 then 'Universe'
      when hhg_num = 42 then 'Everything'
      else 'Fish'
   end
   = 
   'Everything';

Looking at the Exadata cell server statistics, given that the table was over 75 GB, it sure looked like it was offloading:

That’s only 23 MB of data returned to the compute layer from a 75 GB table, so for those 76 rows returned, offloading is happening!

DECODE in the WHERE Clause

The logically equivalent WHERE clause written with DECODE looks like this:

select note_num, note_txt
from notes
where
   decode(hhg_num,
      40,'Life',
      41,'Universe',
      42,'Everything','Fish')
 = 'Everything';

I know that DECODE will offload, and the cell statistics confirm that as well:

Offloading Mystery Solved (mostly)!

What was going on under the covers with CASE? With the help of some Oracle Exadata storage technology experts, I was able to trace what happens in the WHERE clause with the CASE expression:

Looking at the two lines after SAGE LOG, you can see how the WHERE clause is executed. Well, it doesn’t look much like a CASE expression any longer; it looks more like a DECODE function! Oracle has translated a CASE expression to a DECODE, and a DECODE function is offloadable.

Are ALL CASE Expressions in WHERE Clauses Offloadable?

That’s what I’m going to find out, so stay tuned for part 2. Whether the CASE expression is a simple CASE expression or a searched CASE expression, I’m able to rewrite all of them with DECODE, and therefore it’s offloadable. And of course CASE expressions are easier to write, understand, and maintain, so I’ll get the best of both worlds with using CASE in the WHERE clause on Exadata and … no one will get on my case about it!