日期:2014-05-17  浏览次数:20897 次

sql语句在过程里加上变量后执行非常慢
这条语句在sql窗口执行很快
SQL code

 INSERT INTO T_ESHOP_MONTH
    SELECT MON,
           'qry_su',
           DECODE(SUM(TOTAL), 0, 0, SUM(SU) / SUM(TOTAL)),
           'www'
      FROM (SELECT /*+parallel(t,10)*/
             TO_CHAR(T.BIZ_BEGINDATE, 'YYYYMM') MON,
             'qry_num' , COUNT(CASE
               WHEN T.BUSI_RESULT = '0' THEN
                1
             END) SU,
             COUNT(1) TOTAL
              FROM ES_PUB_SYSTEMLOG T
             WHERE T.OPER_TYPE = '0'
               AND T.BIZ_BEGINDATE >= TO_DATE('20120601', 'YYYYMMDD')
               AND T.BIZ_BEGINDATE < TO_DATE('20120701', 'YYYYMMDD')
             GROUP BY TO_CHAR(T.BIZ_BEGINDATE, 'YYYYMM')
            UNION ALL
            SELECT /*+parallel(t,10)*/
             TO_CHAR(T.BIZ_BEGINDATE, 'YYYYMM'),
             'qry_num' , COUNT(CASE
               WHEN T.BUSI_RESULT = '0' THEN
                1
             END) SU,
             COUNT(1) TOTAL
              FROM ES_PUB_SYSTEMLOGMM T
             WHERE T.OPER_TYPE = '0'
               AND T.BIZ_BEGINDATE >= TO_DATE('20120601', 'YYYYMMDD')
               AND T.BIZ_BEGINDATE < TO_DATE('20120701', 'YYYYMMDD')
             GROUP BY TO_CHAR(T.BIZ_BEGINDATE, 'YYYYMM'))
     GROUP BY MON;
  COMMIT;



当加上变量做条件后就执行很慢
SQL code

  INSERT INTO T_ESHOP_MONTH
    SELECT MON,
           'qry_su',
           DECODE(SUM(TOTAL), 0, 0, SUM(SU) / SUM(TOTAL)),
           'www'
      FROM (SELECT /*+parallel(t,10)*/
             TO_CHAR(T.BIZ_BEGINDATE, 'YYYYMM') MON,
             'qry_num' , COUNT(CASE
               WHEN T.BUSI_RESULT = '0' THEN
                1
             END) SU,
             COUNT(1) TOTAL
              FROM ES_PUB_SYSTEMLOG T
             WHERE T.OPER_TYPE = '0'
               AND T.BIZ_BEGINDATE >= V_MONTH_DATE--变量
               AND T.BIZ_BEGINDATE < V_NMONTH --变量
             GROUP BY TO_CHAR(T.BIZ_BEGINDATE, 'YYYYMM')
            UNION ALL
            SELECT /*+parallel(t,10)*/
             TO_CHAR(T.BIZ_BEGINDATE, 'YYYYMM'),
             'qry_num' , COUNT(CASE
               WHEN T.BUSI_RESULT = '0' THEN
                1
             END) SU,
             COUNT(1) TOTAL
              FROM ES_PUB_SYSTEMLOGMM T
             WHERE T.OPER_TYPE = '0'
                AND T.BIZ_BEGINDATE >= V_MONTH_DATE--变量
               AND T.BIZ_BEGINDATE < V_NMONTH --变量
             GROUP BY TO_CHAR(T.BIZ_BEGINDATE, 'YYYYMM'))
     GROUP BY MON;
  COMMIT;



这些表都是分区表,所以按照时间段取得
请高手帮忙解决

------解决方案--------------------
to_char(varchar(8),T.BIZ_BEGINDATE,112) between start and end;
你这样测试一下???
------解决方案--------------------
V_MONTH_DATE V_NMONTH 变量是什么类型?
------解决方案--------------------
没啥好法,把里面的select部分逐步分解,看执行计划