[20170424]函数COALESCE优于NVL.txt
--//以前如果某个字段为NULL，在一些运算要给它赋值，比如0，实际上开发(包括自己更喜欢使用nvl),实际上COALESCE更加优于nvl，
--//参考链接做一个例子：
http://nimishgarg.blogspot.com/2015/01/why-prefer-coalesce-over-nvl.html
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> select nvl(1,2),nvl(null,2) from dual ;
NVL(1,2) NVL(NULL,2)
---------- -----------
1 2
SCOTT@book> select COALESCE(1,2),COALESCE(null,2) from dual ;
COALESCE(1,2) COALESCE(NULL,2)
------------- ----------------
1 2
--//可以发现2者一样，完全可以替换。COALESCE能支持多个参数，例子：
SCOTT@book> select COALESCE(1,null,2),COALESCE(null,null,2) from dual ;
COALESCE(1,NULL,2) COALESCE(NULL,NULL,2)
------------------ ---------------------
1 2
create or replace function f_null return number
is
begin
dbms_lock.sleep(1);
return 0;
end;
/
2.测试:
SCOTT@book> select count(*) from emp where comm is null ;
COUNT(*)
----------
10
--//有10条comm is null的记录。
SCOTT@book> set timing on
SCOTT@book> select emp.*,nvl(emp.comm,f_null) from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO NVL(EMP.COMM,F_NULL)
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- --------------------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 0
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 300
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 500
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 0
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 1400
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 0
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 0
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 0
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 0
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 0
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 0
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 0
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 0
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 0
14 rows selected.
Elapsed: 00:00:14.02
--//14秒，基本每条1秒，实际上从这里就可以看出nvl函数的缺陷，没有采用断路判断,执行14次f_null函数。
SCOTT@book> select emp.*,COALESCE(emp.comm,f_null) from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO COALESCE(EMP.COMM,F_NULL)
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- -------------------------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 0
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 300
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 500
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 0
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 1400
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 0
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 0
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 0
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 0
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 0
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 0
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 0
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 0
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 0
14 rows selected.
Elapsed: 00:00:10.02
--//10秒，实际上从这里就可以看出函数COALESCE采用断路判断,comm存在10条null的记录，调用10次f_null函数。
--//我这个例子不是很好，null很多，不过一样能说明问题，如果大量调用COALESCE优势就很明显了。
