[20170503]]函数COALESCE优于NVL 2.txt
http://blog.itpub.net/267265/viewspace-2137853/
--//上面的链接提示COALESCE具有短路的功能,能很快获得结果,我上次测试采用自定义函数,演示这个功能,实际上的应用不会是变量,可能
--//是常数.做一个测试.
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------- -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> create table t as select rownum id from dual connect by level<=2e4;
Table created.
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
commit;
--//分析表略.表大小128M.
SCOTT@book> select count(*) from t;
COUNT(*)
----------
10240000
2.测试:
SCOTT@book> set timing on
SCOTT@book> select count(*) from t where COALESCE(id,0)=0;
COUNT(*)
----------
0
Elapsed: 00:00:00.60
SCOTT@book> select count(*) from t where nvl(id,0)=0;
COUNT(*)
----------
0
Elapsed: 00:00:00.66
--//差别不是很明显,可以讲差别不大.加入简单运算看看.
SCOTT@book> select count(*) from t where COALESCE(id,0+id)=0;
COUNT(*)
----------
0
Elapsed: 00:00:00.60
SCOTT@book> select count(*) from t where nvl(id,0+id)=0;
COUNT(*)
----------
0
Elapsed: 00:00:00.90
--//从这里也可以看出COALESCE短路判断的优势.虽然不是很明显.加入一点复杂运算看看.
SCOTT@book> select count(*) from t where COALESCE(id,sqrt(id))=0;
COUNT(*)
----------
0
Elapsed: 00:00:00.60
SCOTT@book> select count(*) from t where nvl(id,sqrt(id))=0;
COUNT(*)
----------
0
Elapsed: 00:00:04.32
--//从这里看出差异,而且可以看出nvl先运算了sqrt(id).而采用COALESCE无论何种运算,执行实际基本不变.当然我表中的数据id没有空值.
