I have a query with date and the days function around it as shown.
I rewrote the query removing the days function. The explain shows a reduced cost biut it runs slower than the original query. Can some one explain why it is so.
I am on AIX 5 and DB2 8.2
original query
SELECT count(*)
FROM doc1 d1
INNER JOIN doc2 d2 on (D1.doc_ID = D2.doc_ID)
INNER JOIN doc3 d3 ON (D1.doc_ID = d3.doc_ID)
WHERE d1.type='CCC' and d2.type='VAL'
AND DAYS(D1.I_DT) < (DAYS(current timestamp) - 30)
;
optimized query
SELECT count(*)
FROM doc1 d1
INNER JOIN doc2 d2 on (D1.doc_ID = D2.doc_ID)
INNER JOIN doc3 d3 ON (D1.doc_ID = d3.doc_ID)
WHERE d1.type='CCC' and d2.type='VAL'
and D1.I_DT < current timestamp - 30 days
;
The costs are as follows:
Original query:(takes 20s to return count)
Total cost 609,268.44 timerons
CPU cost 7,062,328,832 instructions
I/O cost 154,628.53 I/Os
First row cost 609,195.25 timerons
Optimized query:(takes 1min8s to return results
Total cost 11,649.26 timerons
CPU cost 44,773,528 instructions
I/O cost 2,755.8 I/Os
First row cost 10,337.4 timerons
Thx,
I rewrote the query removing the days function. The explain shows a reduced cost biut it runs slower than the original query. Can some one explain why it is so.
I am on AIX 5 and DB2 8.2
original query
SELECT count(*)
FROM doc1 d1
INNER JOIN doc2 d2 on (D1.doc_ID = D2.doc_ID)
INNER JOIN doc3 d3 ON (D1.doc_ID = d3.doc_ID)
WHERE d1.type='CCC' and d2.type='VAL'
AND DAYS(D1.I_DT) < (DAYS(current timestamp) - 30)
;
optimized query
SELECT count(*)
FROM doc1 d1
INNER JOIN doc2 d2 on (D1.doc_ID = D2.doc_ID)
INNER JOIN doc3 d3 ON (D1.doc_ID = d3.doc_ID)
WHERE d1.type='CCC' and d2.type='VAL'
and D1.I_DT < current timestamp - 30 days
;
The costs are as follows:
Original query:(takes 20s to return count)
Total cost 609,268.44 timerons
CPU cost 7,062,328,832 instructions
I/O cost 154,628.53 I/Os
First row cost 609,195.25 timerons
Optimized query:(takes 1min8s to return results
Total cost 11,649.26 timerons
CPU cost 44,773,528 instructions
I/O cost 2,755.8 I/Os
First row cost 10,337.4 timerons
Thx,
Comment