CASE statement versus DECODE in WHERE clause on Exadata

Bob Bryla

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.

Gaz in Oz

Your terminology is wrong.

The CASE statement is part of PL/SQL syntax.

The CASE *expression* is part of SQL syntax.

And yes DECODE can be replaced with CASE expressions. Note how ever, in certain circumstances the call to DECODE can still be faster than the CASE expression.

Gaz in Oz

Your terminology is wrong.

The CASE statement is part of PL/SQL syntax.

The CASE *expression* is part of SQL syntax.

And yes DECODE can be replaced with CASE expressions. Note how ever, in certain circumstances the call to DECODE can still be faster than the CASE expression.

Bob Bryla

Thanks for the correction, words matter.

I did a trace of a typical query on Exadata with a CASE expression in the WHERE clause, and all the examples I tested showed that CASE was translated internally to a DECODE and then was able to offload.

Gaz in Oz

Thank you for posting back your findings.