《decode函数的妙用》网友的两个问题解答
《decode函数的妙用》网友的两个问题解答
bisal
发布于 2020-04-12 20:37:20
发布于 2020-04-12 20:37:20
《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));请体会下，如果还是有问题，欢迎提出来，一起讨论解决。
本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2020-04-10 ，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读