《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));
请体会下,如果还是有问题,欢迎提出来,一起讨论解决。