decode-case性能比较.docVIP

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 sizebytes 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 (