Following is the query. The table contains millions of records. Please ignore the column names. Could anyone help me out in optimising this query. Also let me know if you want more information. It consumes 37% of CPU time for my application. What I personally feel is that the problem is with WHERE clause.
01 DECLARE C2 CURSOR FOR SELECT SECT_NO,ORD_SEQ_NO,FLD_ID_TYP,ACTN_CD,FLD_ID_NM,FLD_ID_DA FROM RSRFD10Y
WHERE ST_ID=:H AND PTN_INDR=:H AND SRV_ORD_NO=:H AND ENT_DAT=:H AND ENT_TM=:H AND SECT_NO IN(:H,:H)
AND(FLD_ID_TYP='F' AND ACTN_CD IN(:H,:H) AND FLD_ID_NM IN('DVDP','IHST','TN')) ORDER BY ORD_SEQ_NO
LOCATION: DBZK
1. How many indexes does table have. Are the columns given in WHERE clause with '=' have indexable columns?
If not consider add index and there will be more considerations if you are adding index.
2. SQL also have 'IN' clause but there is only one value in ( .. ), replace these 'IN' clause with '='.
3. Does your application need all the rows retrieved, if not add following to restrict no. of rows retrieved.
Code:
OPTIMIZE FOR n ROWS
FOR FETCH ONLY
4. Does the table have clustering index? If so, is ORD_SEQ_NO part of index in higher order, then rows retrieved will be in sequence.
5. Remember the three R's ( RRR) ==> REORG, RUNSTATS and REBIND.
Schedule REORG, RUNSTATS and REBIND on table, check Clustering Ratio (should be in higher 90's) to determine when 3 R's are neeeded. The 3 R's make SQL whose the best path to retrieve data.
6. How the the BIND done, does the application having this cursor does only retrieves or INSERTS and UPDATES. If it does retrieves only preferably it should have ISOLATION(CS) and CURRENTDATA(NO).
7. Finally run EXPLAIN or Enhanced Explain and verify if SQL is using indexes properly and how filtering is done. Do this after step # 5 ( 3 R's).
Lower the filter factor value the more likely DB2 will use and available index.
