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

爱被打了一巴掌 2023-07-24 11:31 92阅读 0赞

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

有位朋友在后台问到，

![format_png][]

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

**问题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篇文章分类和索引][600]》

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

《[Oracle 19c之RPM安装][Oracle 19c_RPM]》

《[应用执行慢的问题排查路径][Link 1]》

《[ACOUG年会感想][ACOUG]》

《[千万级表数据更新的需求][Link 2]》

《[探寻大表删除字段慢的原因][Link 3]》

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

《[新增字段的一点一滴技巧][Link 4]》

《[对recursive calls的深刻理解][recursive calls]》

《[《Oracle Concept》第三章 - 12][Oracle Concept_ - 12]》

《[一次惊心动魄的问题排查][Link 5]》

《[英超梦幻之行][Link 6]》

《[藤子不二雄博物馆之行][Link 7]》

《[传控Tiki-Taka战术解惑][Tiki-Taka]》

[decode]: https://blog.csdn.net/bisal/article/details/105424100
[format_png]: /images/20230528/645a9315d9c74d5e942b674f55dc4076.png
[600]: https://blog.csdn.net/bisal/article/details/104548761
[Oracle ACE]: https://blog.csdn.net/bisal/article/details/94386471
[Oracle 19c_RPM]: https://blog.csdn.net/bisal/article/details/100909708
[Link 1]: https://blog.csdn.net/bisal/article/details/102480420
[ACOUG]: https://blog.csdn.net/bisal/article/details/103675746
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea814beaddf6c2fcd242591e4892a3ac21a272f3307b9771a8ca9b76ef96b1a7bd8249c564fa&idx=1&mid=2247487001&scene=21&sn=15394f15aedee7f8f261fa7294df08a5#wechat_redirect
[Link 3]: https://blog.csdn.net/bisal/article/details/86635285
[Oracle bug]: https://blog.csdn.net/bisal/article/details/101729688
[Link 4]: https://blog.csdn.net/bisal/article/details/103626212
[recursive calls]: https://blog.csdn.net/bisal/article/details/100074863
[Oracle Concept_ - 12]: https://blog.csdn.net/bisal/article/details/100788405
[Link 5]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea8149e8ddf6c0fea92181efe658b69c6acd52b691cd562e830f0e63a565215a136105272d27&idx=1&mid=2247486491&scene=21&sn=584c4ac132b69a1e319b2a2bedfd49e2#wechat_redirect
[Link 6]: https://blog.csdn.net/bisal/article/details/103044518
[Link 7]: https://blog.csdn.net/bisal/article/details/103379370
[Tiki-Taka]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea814e5cddf6c74a763ac9fac98f94e538bca009fdcff3795533026269686983ceae190fe883&idx=1&mid=2247486383&scene=21&sn=d4c54d7033fc9a21d622e5937cddf64e#wechat_redirect