《decode函数的妙用》网友的两个问题解答

《decode函数的妙用》这篇文章中，提到两种写法，

SQL> select * from emp 
     where id=:id 
       and (name=:name or (name is null and :name is null));
       
SQL> select * from emp
     where id=:id and decode(name, :name, 1)=1;

有位朋友在后台问到，

我尝试着解答下这两个问题。

问题1：这两种写法，效率有什么差异？

假设我们创建复合索引，(id,name)，

SQL> create index idx_e_01 on emp (id, name);
Index created.

从执行计划看，第一种写法，虽然用到了INDEX RANGE SCAN，但是谓词条件显示的，复合索引签到列id用上了索引，where条件中name相关部分，则是作为过滤条件的，

SQL> select * from emp 
     where id=:id and 
     (name=:name or (name is null and :name is null));
        ID NAME
---------- -------------------------
         1 a


-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| IDX_E_01 |     1 |    27 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("ID"=:ID)
       filter(("NAME"=:NAME OR ("NAME" IS NULL AND :NAME IS NULL)))

对第二种写法，同样只是用到了复和索引前导列id，where条件中name相关部分，则是作为过滤条件的，

SQL> select * from emp 
     where id=:id and decode(name, :name, 1)=1;
        ID NAME
---------- -------------------------
         2


-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| IDX_E_01 |     1 |    27 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("ID"=:ID)
       filter(DECODE("NAME",:NAME,1)=1)

从这两条SQL的10053看，cost的值是相同的，

Best so far:  Table#: 0  cost: 1.0002  card: 0.0082  bytes: 27

因此，这两种写法，在效率上，是相同的。

问题2：对第一种写法的理解，尤其是参数 is null？

针对测试数据，(id=1,name='a')和(id=2,name='')，

SQL> select * from emp;
  ID  NAME
---- -------
   1  a
   2

对(id=1,name='a')，这条SQL就够了，

SQL> select * from emp where id=:id and name=:name;

对(id=2,name='')，因为name是空，就会出现"name=null"，但是Oracle中null=null返回的是false，判断空值，需要使用is null或者is not null，按照这种理解，字段name用is null为条件，同时按照语意，输入参数(绑定变量)是null，两个条件加起来，就是如下SQL，

SQL> select * from emp 
     where id=:id and (name is null and :name is null);

再将这两种情况，使用or或的关系，关联起来，就是这条SQL，

SQL> select * from emp
     where id=:id
     and (name=:name or (name is null and :name is null));

请体会下，如果还是有问题，欢迎提出来，一起讨论解决。

近期热文：

《公众号600篇文章分类和索引》

《Oracle ACE，一段不可思议的旅程》

《Oracle 19c之RPM安装》

《应用执行慢的问题排查路径》

《ACOUG年会感想》

《千万级表数据更新的需求》

《探寻大表删除字段慢的原因》

《一次Oracle bug的故障排查过程思考》

《新增字段的一点一滴技巧》

《对recursive calls的深刻理解》

《《Oracle Concept》第三章 - 12》

《一次惊心动魄的问题排查》

《英超梦幻之行》

《藤子不二雄博物馆之行》

《传控Tiki-Taka战术解惑》