Skip to Main Content

CASE statement versus DECODE in WHERE clause on Exadata

Bob BrylaJul 6 2020 — edited Aug 4 2020

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.

Comments
Post Details
Added on Jul 6 2020
3 comments
131 views