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

《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));

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

---来自腾讯云社区的---bisal