In V$SQLFN_METADATA, the DECODE function is marked as "offloadable" on Exadata. DECODE can almost always be rewritten with CASE; the CASE statement (language element?) is not a SQL function per se, but it does seem to offload the same as in these two logically equivalent statements:
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';
select note_num, note_txt
from notes
where
decode(hhg_num,40,'Life',41,'Universe',42,'Everything','Fish')
= 'Everything';
Is a CASE statement always eligible for offloading on Exadata? Is there some kind of transformation happening? Is there any lower level documentation about this, I can't seem to get very deep beyond the offloading rules and V$SQLFN_METADATA. Thanks.