Need to optimize slow query in DB2 using group by on timestamp

Question

Requirement: Number of billing records created Year and provincewise.

Database: DB2 9.5

Below is the query which is used to fetch the details, Its taking huge time, more then 1 hour and getting timedout.

Number of records are as follows in each table. billing : 9 milion cover : 1 milion Customer: 3 milion address: 4 milion.

select year(bill.created), addr.province,count(1) as yearprovicecount  from billing bill 
inner join cover cov  on ( bill.coveragecid = cov.coveragecid) 
inner join customer cust on (cust.customercid= cov.customercid)
inner join address addr on (cust.customercid=addr.customercid)
group by year(bill.created), addr.province;

Created is a timestamp coulmn.

Since there is no where clause in query, The query has become slow. Please let me know if there is any ways I can optimize the query.

Answer 1

Thanks Folks,

I m able extract reports now, Thanks for the suggestions.

1. I removed unwanted joins

2. I added where clause to limit number of rows scanned.

select year(bill.created), addr.province,count(1) as yearprovicecount  from billing bill 
inner join cover cov  on ( bill.coveragecid = cov.coveragecid) 
-- inner join customer cust on (cust.customercid= cov.customercid)
inner join address addr on (cust.customercid=addr.customercid)
where year(billhis.created) = 2014
group by year(bill.created), addr.province;

Note: The current version of DB 9.5 does not support explain plan, Hence was not able to provide.