asked
Indexes Required
What all indexes will be required for a below query like this ? select monthname(CDR.CALL_DATE) C0 , month(CDR.CALL_DATE) C1 , CDR.CALL_DATE C2 , year(CDR.CALL_DATE) C3 , CDR.DESTINATION C4 , sum(CDR.CALL_COST) over (partition by monthname(CDR.CALL_DATE), month(CDR.CALL_DATE), CDR.CALL_DATE, year(CDR.CALL_DATE), CDR.DESTINATION, CDR.CALL_TYPE ) C5 , CDR.CALL_TYPE C6 , sum((CDR.DURATION_SEC * 1.0e0) / 60) over (partition by monthname(CDR.CALL_DATE), month(CDR.CALL_DATE), CDR.CALL_DATE, year(CDR.CALL_DATE), DR.DESTINATION, CDR.CALL_TYPE ) C7 , count(CDR.CALLED_NO) over (partition by monthname(CDR.CALL_DATE), CDR.DESTINATION ) C8 , CDR.CALLED_NO C9 , sum(CDR.CALL_COST) over (partition by CDR.DESTINATION) C10 from GVIADMIN.CDR CDR where CDR.CALL_TYPE = 'INT' and CDR.CALL_DATE >= '2012-10-01' and CDR.CALL_DATE <= '2012-10-31' FOR FETCH ONLY Will 3 indexes will be enough (One on Call_type, One on Call_date, One on Call_type and Call_date and another one on destination) ? Do we also need to have a cluster index ?
Yes, in pretty much 90%+ of cases a clustered index is recommended for any table. After that, why do you think you need 3 indexes rather than one? What other queries run against this data? What other indexes already exist? It's only possible to guess at index requirements by looking at one query. Suggesting an index may cause more problems for the insert, update and delete operations on the data so it is too difficult to be certain without more information.
The partition statements are not going to benefit from an index because you have functions on the columns: month(CDR.CALL_DATE) etc. This will prevent index use. To really see which indexes might be needed, you have to look at the execution plan to see how the query is being resolved. Also, I would absolutely not recommend having an index on Call_Type with another on Call_Type and Call_Date. One of those two indexes won't be used. Because of how indexes are stored and accessed, those are effectively the same two indexes. If you need a compound index with Call_Type as the leading edge then just use that. You won't need an individual index. I'm with @Jonathan too. You need a clustered index.
11 People are following this question.
