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 WNER = 'SYSTEM' THEN 'SYSTEM'
2 WHEN WNER = '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方式的兩種寫法，執行效率沒有明顯的差別。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-617385/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle Decode()函式和CASE語句的比較Oracle函式
- 代替DECODE的CASE
- switch...case && if...else效率比較和優化優化
- eAccelerator的安裝和效能比較
- MySQL 中的 distinct 和 group by 的效能比較MySql
- 堆排序和快速排序效能比較排序
- C#比較dynamic和Dictionary效能C#
- sqlldr和oracle_datapump效能比較SQLOracle
- PyPy 和 CPython 的效能比較測試Python
- Python、JavaScript和Rust的Web效能比較 - AlexPythonJavaScriptRustWeb
- Apache Prefork和Worker模式的效能比較測試Apache模式
- Case表示式與decode（）函式函式
- JAVA IO效能比較Java
- 批量更新效能比較
- 在MongoDB下使用JS和Python的效能比較MongoDBJSPython
- js 深比較和淺比較JS
- Java Bean Copy元件的效能比較JavaBean元件
- 七種WebSocket框架的效能比較Web框架
- [譯] 原生 iOS(Swift) 和 React-Native 的效能比較iOSSwiftReact
- SAP UI5和React的頁面渲染效能比較UIReact
- 雲主機的硬碟IO效能比較硬碟
- PostgreSQL、Redis與Memcached的效能比較 - CYBERTECSQLRedis
- insert的不同場景效能比較
- Linux壓縮工具的效能比較Linux
- ejb 和 javabean的比較JavaBean
- 索引的分析和比較索引
- ImageMagic 和 GraphicsMagick 的比較
- 比較Java與Node.js的併發性和效能- maxantJavaNode.js
- Java中List集合效能比較Java
- 排序演算法效能比較排序演算法
- python 批量resize效能比較Python
- JavaScript 字串連線效能比較JavaScript字串
- 效能比較工具runStats_pkg
- 服務網格Istio、Linkerd和Cilium效能比較
- Go和Python比較的話，哪個比較好？GoPython
- Oracle date 型別比較和String比較Oracle型別
- Apache與Nginx的優缺點、效能比較，到底選擇哪個比較好？ApacheNginx
- 大資料入門課程：Hadoop和spark的效能比較大資料HadoopSpark