oracle10g 如何减少执行 SUM 计算的时间呢?

Question

oracle10g 如何减少执行 SUM 计算的时间呢?　如下. oracle花了20秒去计算这个 SUM 操作. 可以加快些吗?SQL> select count(2) from postedtt;  COUNT(2)
----------
    797817SQL>  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');COUNT(POSTEDtt."ttDATETIME")   SumStake
------------------------------------------- ----------
                                     453803   71478561
plan:
----------------------------------------------------------
Plan hash value: 1879894972---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |    12 |  1462   (1)| 00:00:18 |
|   1 |  SORT AGGREGATE              |              |     1 |    12 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| POSTEDtt |  7710 | 92520 |  1462   (1)| 00:00:18 |
|*  3 |    INDEX RANGE SCAN          | PT_IDX_J     |  3084 |       |  1058   (1)| 00:00:13 |
---------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   3 - access(TO_CHAR(INTERNAL_FUNCTION("ttDateTime"),'YYYY-MM')='2008-03')
message:
----------------------------------------------------------
         15  recursive calls
          0  db block gets
      56330  consistent gets
      55867  physical reads
          0  redo size
        514  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Answer 1

主要是这个耗时间了，执行了全表扫描TO_CHAR(postedtt."ttDateTime", 'YYYY-MM') = '2008-03' 建议用postedtt."ttDateTime">=to_date('2008-03','YYYY-MM')
and postedtt."ttDateTime"<to_date('2008-04','YYYY-MM')来代替还有order by 有必要用个函数吗？直接order by postedtt."ttDateTime" 就行了

Answer 2

SELECT count(postedtt."ttDateTime"), 
       NVL(SUM(postedtt."Stake"), 0) AS "SumStake" 
FROM postedtt 
WHERE postedtt."ttDateTime" >=to_date('2008-03','yyyy-mm') and
      postedtt."ttDateTime" < to_date('2008-04','yyyy-mm')
ORDER BY postedtt."ttDateTime"; 

Answer 3

他的执行计划里面好像显示有使用索引的，改成这样kan kan 
SELECT /*+ parallel(a,5) full(a)*/
count(postedtt."ttDateTime"), 
NVL(SUM(postedtt."Stake"), 0) AS "SumStake" 
FROM postedtt a
WHERE TO_CHAR(postedtt."ttDateTime", 'YYYY-MM') = '2008-03' 
     ORDER BY TO_CHAR(postedtt."ttDateTime", 'YYYY-MM-DD'); 

Answer 4

有这个 index PT_IDX_J is:create index PT_IDX_J
on "postedtt"(
to_char("ttDateTime", 'YYYY-MM')
);只是没有 SUM 计算时, 运行就好快. 一加 SUM 操作就变得好慢.SQL> SELECT count(postedtt."ttDateTime"),
FROM postedtt
WHERE TO_CHAR(postedtt."ttDateTime", 'YYYY-MM') = '2008-03'
ORDER BY TO_CHAR(postedtt."ttDateTime", 'YYYY-MM-DD');COUNT(POSTEDTICKET."TICKETDATE
------------------------------
453803Executed in 0.531 seconds

Answer 5

这句有什么作用呢?  加了反而报错./*+ parallel(a,5) full(a)*/ ORA-00904: "postedtt"."ttDateTime": 标识符无效