|
View previous topic :: View next topic
|
| Author |
Message |
callkris_cit
New User
Joined: 22 Apr 2006
Posts: 44
|
|
|
|
| Code: |
SELECT A.CLM_CD, A.CLM_RANGE,COUNT(*),
SUM(A.AMT_VAL)
FROM KGH.CLM_DTL_TAB A,
WHERE DM_PART_NUM < 40
AND CLM_ID_VAL > 0
AND TIMESTMP_VAL >= '2008-11-31-01.44.09.000008'
GROUP BY A.CLM_CD, A.CLM_RANGE; |
The fields DM_PART_NUM, CLM_ID_VAL, TIMESTMP_VAL present in the Index field.
All the Remining fields not Present in the index
The tablespace REORGed and also INDEX also reorged on December 1st week.
After REORGS, i ran the above query. It ran with in 35 Mins.
And also EXPLAIN shows the above query using INDEX.
Note: This table contain more than Billion records.
In 30 Dec 2008, again the this table loaded with below LOAD CARD option.
| Code: |
LOAD DATA RESUME YES LOG NO
INDDN SYSREC
COPY NO COPYPEND NO
INTO TABLE KGH.CLM_DTL_TAB |
5 Million records loaded on 30 Dec 2008.After that i ran the above query.
The query took more than 3 hours for completion. But this time also EXPLAIN Shows its using the index. But it took more time for completion.
Please let me know the reason for this.
Edited: Please use BBcode when You post some code, that's rather readable, Thanks...Anuj |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Measuring run-time is usually futile - it may be the most visable, but is not directly tunable. How long a process runs depends on many factors such as how much other work is going on in the system in general and how many other tasks are using the same table.
If is very common for run-time to vary greatly between runs.
You might talk with your dba to set up something to measure what db2 resources are used by this query and then compare usage for multiple executions. |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| The query will use index but the matchcols in your case would be 1, because of less equality clauses in your where clause .... 5 million is quite a huge number for aggregrate functions .... as dick suggested find out from your DBA .. |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
CallKris,
I had a similar problem, but query wasn't accessing BILLION records. But, it goes through a lot of computations, it took lot of time. So we came out with MQT's, which saved lot of time. If your query is not gonna change, i can say you can go for MQT.
Waiting for EXPERTS ADVICE on this!
Sushanth Bobby |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| Incase the Query is run only once after every load MQT's can be used or else an overhead of frequent REFRESH will be there |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Ashimer,
| Quote: |
| Incase the Query is run only once after every load |
In this case, MQT are of no-use. Query is enough.
Sushanth |
|
| Back to top |
|
|
|
|
