CASE statement versus DECODE in WHERE clause on Exadata — oracle-tech

    Forum Stats

  • 3,715,753 Users
  • 2,242,856 Discussions
  • 7,845,557 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

CASE statement versus DECODE in WHERE clause on Exadata

Bob Bryla
Bob Bryla Member Posts: 106 Blue Ribbon
edited August 2020 in Exadata

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.

Tagged:
Gaz in Oz

Best Answer

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,754 Bronze Crown
    edited August 2020 Accepted Answer

    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.

Answers

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,754 Bronze Crown
    edited August 2020 Accepted Answer

    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
    Bob Bryla Member Posts: 106 Blue Ribbon
    edited August 2020

    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.

    sage_log.PNG

    Gaz in Oz
  • Gaz in Oz
    Gaz in Oz Member Posts: 3,754 Bronze Crown
    edited August 2020

    Thank you for posting back your findings.

Sign In or Register to comment.