这个SQL如何优化？

huang_w 2012-07-23 02:30:12



select A.ACC_DATE,

       DECODE(  SIGN( TO_CHAR( A.ACC_DATE,'MM' ) - '07')   , -1 ,  TO_CHAR(A.ACC_DATE,'YYYY')||'0101-'||TO_CHAR(A.ACC_DATE,'YYYY')||'0630' ,  TO_CHAR(A.ACC_DATE,'YYYY')||'0701-'||TO_CHAR(A.ACC_DATE,'YYYY')||'1231' ) HALFYEAR,

       DECODE(  SIGN( TO_CHAR( A.ACC_DATE,'MM' ) - '07')   , -1 ,  TO_CHAR(A.ACC_DATE,'YYYY')||'年上半年' ,  TO_CHAR(A.ACC_DATE,'YYYY')||'年下半年' ) HALFYEARNAME,

       A.ORGID,A.CURR_TYPE,A.ITM_NO,DECODE(C.SUBDIRE,'1',-1,'0',0,'2',1  ) SUBDIRE,A.ACC_SET,DECODE (SUBSTR(A.ITM_NO,1,1) ,'1','1','2','2','3','3','4','4','5','5','6','5','6') ITM_TYPE,

       A.DEBAMT ,

       A.CREAMT ,

       A.DEBBLC,

       A.CREBLC,

       A.DEBBLC DEBBLC1,

       A.CREBLC CREBLC1, 

       A.DEBBLC DEBBLC2,

       A.CREBLC CREBLC2, 

       DECODE(SUBSTR(A.ITM_NO,1,1), 

           '1',A.DEBBLC - A.CREBLC,

           '3',A.DEBBLC - A.CREBLC,

           '6',A.DEBBLC - A.CREBLC,

               A.CREBLC-A.DEBBLC)   BLC,

       DECODE(SUBSTR(A.ITM_NO,1,1) , 

           '2',A.CREAMT,

           '4',A.CREAMT,

           '5',A.CREAMT ,

               A.DEBAMT )   AMT,

       (SELECT SUM( DECODE(SUBSTR(T.ITM_NO,1,1), 

                      '1',T.DEBBLC - T.CREBLC,

                      '3',T.DEBBLC - T.CREBLC,

                      '6',T.DEBBLC - T.CREBLC,

                          T.CREBLC-T.DEBBLC))  

         FROM  T_RPT_FACT_01SUBAMT T WHERE T.ORGID = A.ORGID

         AND   T.ITM_NO = A.ITM_NO

         AND   T.ACC_SET = A.ACC_SET

         AND   T.CURR_TYPE = A.CURR_TYPE

         AND   T.TERMTYPE = A.TERMTYPE

         AND   T.ACC_DATE = TRUNC(A.ACC_DATE, 'YY') - 1                  

        ) BYBLC, ---年初余额                 

       B.ORGID1 ,B.ORGID2,B.ORGID3,B.ORGID4,B.ORGID5,B.ORGID6,

       B.ORGNAME1,B.ORGNAME2,B.ORGNAME3,B.ORGNAME4,B.ORGNAME5,B.ORGNAME6,

       B.AREAID1 , B.AREAID2 , B.AREANAME1 , B.AREANAME2 ,

       B.ORGPROP,

       B.ORGCATEGORY,

       C.ITM_NO1 , C.ITM_NAME1 , C.SHORTNAME1 ,

       C.ITM_NO2 , C.ITM_NAME2 , C.SHORTNAME2 ,

       C.ITM_NO3 , C.ITM_NAME3 , C.SHORTNAME3 ,

       C.ITM_NO4 , C.ITM_NAME4 , C.SHORTNAME4 ,

       C.ITM_NO5 , C.ITM_NAME5 , C.SHORTNAME5  

from   T_RPT_FACT_01SUBAMT A,T_RPT_DIM_ORG B ,T_RPT_DIM_SUB C,T_RPT_SYS_ACCDATE_CUBE D 

where A.ORGID =B.ORGID   AND B.ORGTYPE='1' 

      AND A.ITM_NO = C.ITM_NO  AND C.ISENDSUB='1' AND C.ITM_NO <'7'

      AND D.CUBETYPE='3' AND A.ACC_DATE>=D.STARTDATE AND  A.ACC_DATE <= D.ENDDATE   AND D.STATUS='1'

      AND TO_CHAR(A.ACC_DATE + 1,'dd')='01'----这里是只取每月的最后一天

      --其中事实表T_RPT_FACT_01SUBAMT 索引只有一个，字段包括：ORGID，ITM_NO，ACC_SET，CURR_TYPE，TERMTYPE，ACC_DATE