[20170503]]函式COALESCE優於NVL 2.txt

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