【原创-每日一优】count/sum优化

【原理】
SQL> select count(*) from emp where deptno=20;

  COUNT(*)
----------
         5
   
SQL> select sum(decode(deptno, 20, 1, null)) from emp;

SUM(DECODE(DEPTNO,20,1))
------------------------
                       5


SQL> select count(decode(deptno, 20, 1, null)) from emp;

COUNT(DECODE(DEPTNO,20,1))
--------------------------
                         5
【案例】
http://www.itpub.net/thread-964972-1-1.html
SELECT    count(postedtt."ttDateTime"),
    NVL(SUM(postedtt."Stake"), 0) AS "SumStake"
    FROM    postedtt
    WHERE    TO_CHAR(postedtt."ttDateTime", 'YYYY-MM') = '2008-03'
        ORDER BY TO_CHAR(postedtt."ttDateTime", 'YYYY-MM-DD');

优化后
SELECT    count(decode(TO_CHAR(t.ttDateTime, 'YYYY-MM'), '2008-03', 1, null),
FROM    postedtt t
union all
select    NVL(SUM(t.Stake), 0)
FROM    postedtt t
WHERE    TO_CHAR(t.ttDateTime, 'YYYY-MM') = '2008-03'
/

create a index for ttDateTime column and analyze it