oracle函數之case和decode的用法區別及效能比較

    在oracle世界，你可以使用：

    1）case運算式      或者

    2）decode函數

    來實現邏輯判斷。Oracle的DECODE函數功能很強，靈活運用的話可以避免多次掃描，從而提高查詢的效能。而CASE是9i以後提供的文法，這個文法更加的靈活，提供了IF
THEN ELSE的功能。   

    case運算式

    case運算式，可分兩種，簡單和搜尋，簡單case後接運算式，如：

    對於簡單的case需要幾點注意：

    1）尋找when的優先順序：從上到下

    2）再多的when，也只有一個出口，即其中有一個滿足了expr就馬上退出case

    3）不能把return_expr和else_expr指定為null，而且，expr、comparison_expr和return_expr的資料類型必須相同。

    搜尋case：

    CASE WHEN condition THEN return_expr

              [WHEN condition THEN return_expr]

              ...

    ELSE else_expr

    END

    例子：

SELECT (CASE WHEN cust_credit_limit BETWEEN  0 AND 3999 THEN  ' 0 - 3999'   WHEN cust_credit_limit BETWEEN  4000 AND 7999 THEN ' 4000 - 7999'   WHEN cust_credit_limit BETWEEN  8000 AND 11999 THEN  ' 8000 - 11999'   WHEN cust_credit_limit BETWEEN  12000 AND 16000 THEN '12000 - 16000' END)  AS BUCKET, COUNT(*) AS Count_in_GroupFROM customers WHERE cust_city = 'Marshal' GROUP BY (CASE WHEN cust_credit_limit BETWEEN  0 AND 3999 THEN ' 0 - 3999' WHEN cust_credit_limit BETWEEN  4000 AND 7999 THEN ' 4000 - 7999' WHEN cust_credit_limit BETWEEN  8000 AND 11999 THEN  ' 8000 - 11999' WHEN cust_credit_limit BETWEEN  12000 AND 16000 THEN '12000 - 16000' END);BUCKET        COUNT_IN_GROUP------------- -------------- 0 - 3999                  8 4000 - 7999               7 8000 - 11999              712000 - 16000              1

    用decode可以違反第3NF（行不可再分，列不可再分，列不可重複）：列重複

hr@ORCL> select * from a;        ID NAME---------- ----------         1 a         2 b         3 c         1 ahr@ORCL> select sum(decode(id,1,1,0)) think,  2             sum(decode(id,2,2,0)) water,  3             sum(decode(id,3,3,0)) linshuibin  4        from a;     THINK      WATER LINSHUIBIN---------- ---------- ----------         2          2          3

 

    一個欄位，decode函數可以完全改寫簡單case；

    多個欄位，需要複雜的case，方可。

    文法：
    DECODE(value,if1,then1,if2,then2,if3,then3,...,else)，表示如果value等於if1時，DECODE函數的結果返then1,...,如果不等於任何一個if值，則返回else。可以用函數或運算式來替代value，if，then，else從而作出一些更有用的比較。

    來看看具體的運用：
    1 假設我們想給百度職員加工資，其標準是：工資在8000元以下的將加20％；工資在8000元以上的加15％
    則：

    select decode(sign(salary - 8000),1,salary*1.15,-1,salary*1.2,salary) "revised_salary" from employee

    2 表table_subject，有subject_name列。要求按照：語、數、外的順序進行排序
    則：

    select * from table_subject order by decode(subject_name, '語文', 1, '數學', 2, , '外語',3)

    decode和簡單case的效能比較

 

   

Oracle的DECODE函數功能很強，靈活運用的話可以避免多次掃描，從而提高查詢的效能。而CASE是9i以後提供的文法，這個文法更加的靈活，提供了IF
THEN ELSE的功能。

對於很多情況，DECODE和CASE都能解決問題，個人更傾向於使用DECODE，一方面是從8i保留下來的習慣，另一方面是DECODE的文法更加的簡潔，代碼量要小一些。

不過今天在看Oracle9i的資料倉儲手冊時發現，Oracle在文檔中提到CASE語句的效率會更高一些，尤其是CASE運算式 WHEN
常量 THEN的文法，效率要比CASE WHEN運算式 THEN的文法更高一些。對於後面這種說法倒是沒有太多的疑問，對於CASE比DECODE效率高這種說法倒是第一次看到，印象中DECODE效率很高，應該不會比CASE的效率差。

到底效率如何，還是要具體的執行個體來說：

SQL> CREATE TABLE T AS
2 SELECT A.*
3 FROM DBA_OBJECTS A, DBA_MVIEWS;

Table created.

SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
6075760

下面檢查DECODE和兩種CASE語句的效率：

SQL> SET ARRAY 1000
SQL> SET TIMING ON
SQL> SET AUTOT TRACE
SQL> SELECT DECODE(OWNER, 'SYSTEM', 'SYSTEM', 'SYS', 'SYSTEM', 'USER')
2 FROM T;

6075760 rows selected.

Elapsed: 00:00:07.24

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4245K| 68M| 13828 (1)| 00:03:14 |
| 1 | TABLE ACCESS FULL| T | 4245K| 68M| 13828 (1)| 00:03:14 |
--------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
47551 consistent gets
0 physical reads
0 redo size
46288564 bytes sent via SQL*Net to client
67317 bytes received via SQL*Net from client
6077 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6075760 rows processed

SQL> SELECT CASE OWNER WHEN 'SYSTEM' THEN 'SYSTEM'
2 WHEN 'SYS' THEN 'SYSTEM'
3 ELSE 'USER' END
4 FROM T;

6075760 rows selected.

Elapsed: 00:00:07.22

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4245K| 68M| 13828 (1)| 00:03:14 |
| 1 | TABLE ACCESS FULL| T | 4245K| 68M| 13828 (1)| 00:03:14 |
--------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
47551 consistent gets
0 physical reads
0 redo size
46288578 bytes sent via SQL*Net to client
67317 bytes received via SQL*Net from client
6077 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6075760 rows processed

SQL> SELECT CASE WHEN OWNER = 'SYSTEM' THEN 'SYSTEM'
2 WHEN OWNER = 'SYS' THEN 'SYSTEM'
3 ELSE 'USER' END
4 FROM T;

6075760 rows selected.

Elapsed: 00:00:07.23

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4245K| 68M| 13828 (1)| 00:03:14 |
| 1 | TABLE ACCESS FULL| T | 4245K| 68M| 13828 (1)| 00:03:14 |
--------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
47551 consistent gets
0 physical reads
0 redo size
46288585 bytes sent via SQL*Net to client
67317 bytes received via SQL*Net from client
6077 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6075760 rows processed

測試結果確實是CASE的簡單運算式寫法效率最高，然後是CASE的另一種寫法，DECODE效率最低。但是對於600W的記錄，最終結果只有0.01到0.02秒的查詢，實在沒有辦法得出上面的結論，因為這個差別實在是太小，以至於任何其他的一些影響都足以改變測試結果，如要一定要得出結論，那麼結論就是3種方式的效率基本相同。

不過由於CASE運算式更加靈活，使得以前DECODE必須運用的一些技巧得以簡化，這時使用CASE方式，確實可以得到一些效能上的提高，比如：

SQL> SELECT DECODE(SIGN(OBJECT_ID), 1, '+', -1, '-', '0')
2 FROM T;

6075760 rows selected.

Elapsed: 00:00:04.94

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4245K| 52M| 13840 (1)| 00:03:14 |
| 1 | TABLE ACCESS FULL| T | 4245K| 52M| 13840 (1)| 00:03:14 |
--------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
47551 consistent gets
0 physical reads
0 redo size
31491431 bytes sent via SQL*Net to client
67317 bytes received via SQL*Net from client
6077 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6075760 rows processed

SQL> SELECT CASE WHEN OBJECT_ID > 0 THEN '+'
2 WHEN OBJECT_ID < 0 THEN '-'
3 ELSE '0' END
4 FROM T;

6075760 rows selected.

Elapsed: 00:00:04.60

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4245K| 52M| 13840 (1)| 00:03:14 |
| 1 | TABLE ACCESS FULL| T | 4245K| 52M| 13840 (1)| 00:03:14 |
--------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
47551 consistent gets
0 physical reads
0 redo size
31491449 bytes sent via SQL*Net to client
67317 bytes received via SQL*Net from client
6077 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6075760 rows processed

這裡CASE帶來效能提升的主要原因實際上是CASE避免了SIGN函數的調用，而並不是CASE本身的效能要高於DECODE，事實上如果這裡使用SIGN並利用CASE的所謂高效文法：

SQL> SELECT CASE SIGN(OBJECT_ID) WHEN 1 THEN '+'
2 WHEN -1 THEN '-'
3 ELSE '0' END
4 FROM T;

6075760 rows selected.

Elapsed: 00:00:04.97

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4245K| 52M| 13840 (1)| 00:03:14 |
| 1 | TABLE ACCESS FULL| T | 4245K| 52M| 13840 (1)| 00:03:14 |
--------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
47551 consistent gets
0 physical reads
0 redo size
31491445 bytes sent via SQL*Net to client
67317 bytes received via SQL*Net from client
6077 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6075760 rows processed

可以看到，這時效率比DECODE還低。

根據上面的測試可以得出結論，無論是DECODE還是CASE方式的兩種寫法，執行效率沒有明顯的差別。