decode函数再挖掘
一条SQL，除了满足功能上的需求，更重要的是考虑性能，这就对开发人员、DBA提出了更高的要求，但是我认为这是对技术有追求的朋友们提出的最基础的要求，同样是对我们所研发系统负责的一种态度。
我们知道，创建函数索引的时候，一定用的是常量，如果用绑定变量，则无法使用索引，因为Oracle不能基于未知的用户输入创建索引数据。
对这条SQL，decode函数是无法创建函数索引的，此时能对id创建单键值索引，如果id区分度很好，而且deocde函数的过滤作用很大，这条SQL的性能，就会很好，
SQL> select * from emp
where id=:id and decode(name, :name, 1)=1;如果非要将where条件字段都加上索引，可以改写如下，替换decode函数，创建了这个(id,name)的复合索引，
SQL> select * from emp
where id=:id
and (name=:name or (name is null and :name is null))
SQL> create index idx_emp_01 on emp(id, name);
Index created.但是，他的执行计划，我们看到，只使用了这个复合索引的前导列id，name作为过滤条件，
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_EMP_01 | 1 | 9 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=TO_NUMBER(:ID))
filter("NAME"=:NAME OR "NAME" IS NULL AND :NAME IS NULL)问题来了，能否用到这个复合索引的所有字段？此时，尝试用这个HINT，
SQL> select /*+ use_concat */ * from emp where id=:id and (name=:name or (name is null and :name is null));
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 18 | 2 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
|* 2 | FILTER | | | | | |
|* 3 | INDEX RANGE SCAN| IDX_EMP_01 | 1 | 9 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_EMP_01 | 1 | 9 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:NAME IS NULL)
3 - access("ID"=TO_NUMBER(:ID) AND "NAME" IS NULL)
4 - access("ID"=TO_NUMBER(:ID) AND "NAME"=:NAME)
filter(LNNVL("NAME" IS NULL) OR LNNVL(:NAME IS NULL))发现id、name(=:name和is null)字段，在两个步骤中，都是用到了索引，按照执行计划，先是通过"ID"=TO_NUMBER(:ID) AND "NAME" IS NULL扫描索引得到结果集，然后过滤条件:NAME IS NULL，得到中间结果集1，再通过"ID"=TO_NUMBER(:ID) AND "NAME"=:NAME扫描索引，以及LNNVL("NAME" IS NULL) OR LNNVL(:NAME IS NULL)过滤，得到中间结果集2，中间结果集1和2通过CONCATENATION得到执行结果。
USE_CONCAT这个HINT提示强迫优化器扩展查询中的每一个OR谓词为独立的查询块，最后合并所有查询块的结果，返回结果集给用户。其实就相当于改写为了union all的形式，如下所示，Oracle就会各自计算union all关联的两条SQL执行路径，进而选择索引，
SQL> select * from emp where id = :id and name = :name
2 union all
3 select * from emp where id = :id and (name is null and :name is null) and lnnvl(name = :name);
Execution Plan
----------------------------------------------------------
Plan hash value: 122313535
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 18 | 2 (0)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
|* 2 | INDEX RANGE SCAN | IDX_EMP_01 | 1 | 9 | 1 (0)| 00:00:01 |
|* 3 | FILTER | | | | | |
|* 4 | INDEX RANGE SCAN| IDX_EMP_01 | 1 | 9 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=TO_NUMBER(:ID) AND "NAME"=:NAME)
3 - filter(:NAME IS NULL)
4 - access("ID"=TO_NUMBER(:ID) AND "NAME" IS NULL)
filter(LNNVL("NAME"=:NAME))但是，如果SQL中OR的条件很多，CBO花在分析执行路径上的时间和成本都会相当大，这就可能造成COST成本增加，执行效率下降。
当然，关于这个问题，还可以得到一些其他的结论，例如最好不要在索引字段中使用null，例如绑定变量如果可能为空，可以在程序中进行下判断，传入值为空，一种写法，不为空，另一种写法，通过逻辑调整，就可能带来性能上的明显提升，避免隐患。
其实，这几篇和decode函数相关的文章，只是一个引子，用来说明任何一个看着很小的知识点，深究起来，可能蕴藏着丰富的知识，eygle曾经说过，“学习知识就要由点及面”，对待任何的问题，你比别人多研究一点，你就会得到多一点的收获，经验就是这种一点一滴累积起来的。