|
neellotus
Starting Member
15 Posts |
 Posted - 2013-02-22 : 05:09:51
|
| Hi All,I have created view as per below query but it is taking huge time to show all result so i want to create indexes on this view, pls help me which columns need to create indexes:-CREATE VIEW STK_DETAIL AS SELECT 1 AS ROWID,E.CODE,E.A_CODE,E.I_CODE,E.ZZSF_LOT,E.MRN_NO,E.MRN_CAT,E.URATE,E.QTY,E.QTY1,(E.QTY-E.QTY1) PEND_QTY FROM (SELECT C.CODE,C.A_CODE,C.I_CODE,C.ZZSF_LOT,C.MRN_NO,C.MRN_CAT,AVG(C.URATE) URATE,SUM(C.QTY) QTY,SUM(C.QTY1) QTY1 FROM (SELECT A.DOC_CAT,A.DOC_NO,A.DOC_DT,A.GD_CD,A.A_CODE,A.I_CODE,A.UNIT,A.ZZSF_LOT,A.MRN_CAT,A.MRN_NO,A.URATE,a.QTY1 AS QTY, 0 AS QTY1,A.CODE FROM PENDING A WHERE A.DOC_CAT='ISV1' AND a.DOC_CLASS='06' and a.qty_ytdc=0 UNION ALL SELECT A.DOC_CAT,A.DOC_NO,A.DOC_DT,'' GD_CD,A.A_CODE,A.I_CODE,A.UNIT,A.ZZSF_LOT,A.MRN_CAT,A.MRN_NO,A.URATE,a.QTY1 AS QTY, 0 AS QTY1,A.CODE FROM PEND_FISL A WHERE A.DOC_CAT='FISL' AND a.DOC_CLASS='06' and a.qty_ytdc=0 UNION ALL SELECT A.DOC_CAT,A.DOC_NO,A.DOC_DT,a.GD_CD,A.A_CODE,A.I_CODE,A.UNIT,a.ZZSF_LOT,a.MRN_CAT,A.MRN_NO,A.URATE,A.QTY3 AS QTY, 0 AS QTY1,A.CODE FROM ISSUE A WHERE A.DOC_CAT='FIS1' AND a.DOC_CLASS='06' UNION ALL SELECT A.DOC_CAT,A.DOC_NO,A.DOC_DT,'' GD_CD,A.A_CODE,A.I_CODE,A.UNIT,a.ZZSF_LOT,a.MRN_CAT,A.MRN_NO,A.URATE,A.QTY AS QTY, 0 AS QTY1,A.CODE FROM CTP_B A WHERE A.DOC_CAT='MTPR' UNION ALL SELECT B.DOC_CAT,B.DOC_NO,B.DOC_DT,'' GD_CD,B.A_CODE,B.I_CODE,B.UNIT,D.ZZSF_LOT,D.MRN_CAT,D.MRN_NO,D.URATE,0 AS QTY,D.QTY AS QTY1, b.CODE FROM PTM_A B, PTM_B D WHERE B.DOC_CAT+B.DOC_NO=D.DOC_CAT+D.DOC_NO UNION ALL sELECT B.DOC_CAT,B.DOC_NO,B.DOC_DT,'' GD_CD,B.A_CODE,B.I_CODE,B.UNIT,D.ZZSF_LOT,D.MRN_CAT,D.MRN_NO,D.URATE,0 AS QTY,D.QTY AS QTY1, b.CODE FROM DIPF_A B, DIPF_B D WHERE B.DOC_CAT+B.DOC_NO=D.DOC_CAT+D.DOC_NO AND NOT ISNULL(D.ZZSF_LOT,'')='') C GROUP BY C.CODE,C.A_CODE,C.I_CODE,C.ZZSF_LOT,C.MRN_NO,C.MRN_CAT) E WHERE (E.QTY-E.QTY1) >0 Pls tell me i will be very thankful to you.Neel |
|
how to choose best columns to create indexes