SELECT A.CLM_CD, A.CLM_RANGE,COUNT(*),
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.
LOAD DATA RESUME YES LOG NO
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
Joined: 23 Nov 2006 Posts: 19270 Location: Inside the Matrix
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.
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 ..
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!