关于decode函数,写了《decode函数的妙用》和《《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曾经说过,“学习知识就要由点及面”,对待任何的问题,你比别人多研究一点,你就会得到多一点的收获,经验就是这种一点一滴累积起来的。
近期热文:
《last_load_time和last_active_time的选择》
《虚拟内存详解》