先做1000万的测试数据,生成100以内的随机数。
create table test_tab as
select round(dbms_random.value(0,100)) sz from xmltable('1 to 10000000');
将部分数据设置为空值null
update test_tab set sz = null where sz =10;
打开sqlplus计时器;
SQL> set timing on;
使用decode函数执行;
SQL> select sum(decode(sz,null,1,0))/count(1) from test_tab ;
SUM(DECODE(SZ,NULL,1,0))/COUNT
------------------------------
0.0099949
Executed in 4.322 seconds
使用nvl2函数执行;
SQL> select sum(nvl2(sz,0,1))/count(1) from test_tab ;
SUM(NVL2(SZ,0,1))/COUNT(1)
--------------------------
0.0099949
Executed in 1.623 seconds
使用case语句执行;
SQL> select sum(CASE when sz is null then 1 else 0 end)/count(1) from test_tab ;
SUM(CASEWHENSZISNULLTHEN1ELSE0
------------------------------
0.0099949
Executed in 1.049 seconds
根据上面的用时结果显示可见Case 语句是执行效率最高的!