| How to decrease time to execute SUM function? [message #310640] |
Tue, 01 April 2008 22:15  |
wxfjordan
Messages: 92
Registered: December 2006
|
Member |
|
|
How to decrease time to execute SUM function? Like the following:
It always cost much time to execute SUM function.
Give me some direction.
SQL> 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
|
|
|
|
| Re: How to decrease time to execute SUM function? [message #310665 is a reply to message #310640] |
Wed, 02 April 2008 00:16  |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
> It always cost much time to execute SUM function.
Specify "much" (in comparition with "little" without it).
Maybe you would benefit with putting "Stake" into index PT_IDX_J.
However if your statistics are correct, 7710 rows selected from "POSTEDtt" are not so much, so it should take reasonable time (even when taken through index). Is that optimizer's estimation correct? How many rows are in "POSTEDtt"? Do you frequently insert/delete data from it?
|
|
|
|
|
|
|
|
| Re: How to decrease time to execute SUM function? [message #310712 is a reply to message #310696] |
Wed, 02 April 2008 02:44 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Check the plan for that last SQL. You will find it is performing a FAST FULL SCAN of an index.
This is much faster than the index range scan you are performing in the other query with associated table access.
As suggested by @flyboy, you could add "ttDateTime" to the PT_IDX_J index and avoid the table lookup. That would be better.
It's got nothing to do with the SUM(), except for the fact that you are summing a non-indexed column.
Ross Leishman
|
|
|
|
| Re: How to decrease time to execute SUM function? [message #310715 is a reply to message #310640] |
Wed, 02 April 2008 03:06 |
wxfjordan
Messages: 92
Registered: December 2006
|
Member |
|
|
Oh! I have added "ttDateTime" before.
The index PT_IDX_J is:
create index PT_IDX_J
on "postedtt"(
to_char("ttDateTime", 'YYYY-MM')
);
Mr. rleishman, you are right. "FAST FULL SCAN of an index"
If I change SQL to the following. it's very fast. Just add SUM function will become very slow. Wooo...
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
------------------------------
453803
Executed in 0.531 seconds
|
|
|
|
| Re: How to decrease time to execute SUM function? [message #311097 is a reply to message #310715] |
Thu, 03 April 2008 07:05 |
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
|
Senior Member |
|
|
ya , jordon i am also tired of sum() , and if u find a solution , please do let me.
at times i have used my own function as Mysum() , where possible and it has performed much better.
but its always very confusing
?? good luck
and do share your solution
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
