[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没有空值.