麻烦看看此SQL能如何优化。

Question

需求：
显示可销天数，7天平均数量，30天平均数量，季度平均数量的显示。
问题：
此SQL中有张单表数据达到2400W。(需要优化.)
SQL,此SQL还未完结.现在只显示了可销天数,7天销量.剩下的字段还未显示.(但实现的方法和7天比较类似)SELECT *
  FROM (SELECT A.*, ROWNUM RN
          FROM (select a.mc, a.spm, a.xssl, b.xssl1, a.kcsl
                  from (select tb.name mc, ta.spm, avg(ta.xssl) xssl, ta.kcsl
                          from CC_JXC_GYS_DAY ta,
                               (select * from inf_shop where fgs = 'C') tb
                         where ta.gys = '10035'
                           and ta.mc = tb.code
                           and ta.rq < add_months(sysdate, -1)
                         group by tb.name, ta.spm, ta.kcsl
                         order by kcsl desc) a,
                       (select tb.name mc,
                               ta.spm,
                               avg(ta.xssl) xssl1,
                               ta.kcsl kcsl
                          from CC_JXC_GYS_DAY ta,
                               (select * from inf_shop where fgs = 'C') tb
                         where ta.gys = '10035'
                           and ta.mc = tb.code
                           and to_char(ta.rq, 'yyyy-mm-dd') =
                               to_char(sysdate - 7, 'yyyy-mm-dd')
                         group by tb.name, ta.spm, ta.kcsl
                         order by kcsl desc) b) a
         WHERE ROWNUM <= 1000)
 WHERE RN >= 21PL/SQL解释计划：SELECT STATEMENT, GOAL = ALL_ROWS
耗费=5782 基数=1000 字节=106000
 VIEW 对象所有者=ZON100
耗费=5782 基数=1000 字节=106000
  COUNT STOPKEY
   NESTED LOOPS
耗费=5782 基数=1000 字节=93000
    VIEW
对象所有者=xx 耗费=197 基数=500 字节=6500
     SORT GROUP BY
耗费=181231 基数=924 字节=64680
      HASH JOIN 耗费=181230 基数=924 字节=64680
       TABLE ACCESS FULL
对象所有者=xx 对象名称=INF_SHOP 耗费=6 基数=173 字节=3806
       TABLE ACCESS FULL
对象所有者=xx 对象名称=CC_JXC_GYS_DAY 耗费=181223 基数=6648 字节=319104
    VIEW 对象所有者=ZON100
耗费=5585 基数=1000 字节=80000
     SORT GROUP BY
耗费=181752 基数=32543 字节=2278010
      HASH JOIN
耗费=181212 基数=32543 字节=2278010
       TABLE ACCESS FULL
对象所有者=xx 
对象名称=INF_SHOP 耗费=6 基数=173 字节=3806
       TABLE ACCESS FULL
对象所有者=xx 
对象名称=CC_JXC_GYS_DAY 耗费=181204 基数=234194 字节=11241312
寻求您的耐心指点。万分感谢。

Answer 1

子查询里放order by有什么含义  去掉吧

Answer 2

SELECT *
  FROM (SELECT A.*, ROWNUM RN
          FROM (SELECT A.MC, A.SPM, A.XSSL, B.XSSL1, A.KCSL
                  FROM (SELECT TB.NAME MC, TA.SPM, AVG(TA.XSSL) XSSL, TA.KCSL
                          FROM INFO_SHOP TB,CC_JXC_GYS_DAY TA,
                         WHERE TB.FGS = 'C'
                           AND TA.GYS = '10035'
                           AND TA.MC = TB.CODE
                           AND TA.RQ < ADD_MONTHS(SYSDATE, -1)
                         GROUP BY TB.NAME, TA.SPM, TA.KCSL) A,
                       (SELECT TB.NAME MC,
                               TA.SPM,
                               AVG(TA.XSSL) XSSL1,
                               TA.KCSL KCSL
                          FROM INFO_SHOP TB,CC_JXC_GYS_DAY TA
                         WHERE TB.FGS = 'C'
                           AND TA.GYS = '10035'
                           AND TA.MC = TB.CODE
                           AND TRUNC(TA.RQ) = TRUNC(SYSDATE - 7)
                         GROUP BY TB.NAME, TA.SPM, TA.KCSL) B) A
         WHERE ROWNUM <= 1000
         ORDER BY KCSL DESC)
 WHERE RN >= 21;小弟愚见，看看是否有帮助

Answer 3

可在CC_JXC_GYS_DAY表的gys、mc、rq字段建立索引

Answer 4

以建立了。
就是这3个字段。
但不知道为什么没有走索引。
有的又走了。select a.mc, a.spm, a.xssl, b.xssl1, a.kcsl
  from (select tb.name mc, ta.spm, round(avg(ta.xssl)) xssl, ta.kcsl
          from CQ_JXC_GYS_DAY ta,
               (select * from inf_shop where fgs = 'Q') tb
         where ta.gys = '10315'
           and ta.mc = tb.code
           and ta.rq < add_months(sysdate, -1)
         group by tb.name, ta.spm, ta.kcsl) a,
       (select tb.name mc, ta.spm, round(avg(ta.xssl)) xssl1, ta.kcsl
          from CQ_JXC_GYS_DAY ta,
               (select * from inf_shop where fgs = 'Q') tb
         where ta.gys = '10315'
           and ta.mc = tb.code
           and to_char(ta.rq, 'yyyy-mm-dd') =
               to_char(sysdate - 7, 'yyyy-mm-dd')
         group by tb.name, ta.spm, ta.kcsl) b
 order by kcsl descSELECT STATEMENT, GOAL = ALL_ROWS 耗费=141075 基数=252068 字节=23442324
 SORT ORDER BY 耗费=141075 基数=252068 字节=23442324
  MERGE JOIN CARTESIAN 耗费=135678 基数=252068 字节=23442324
   VIEW 对象所有者=ZON100 耗费=839 基数=82 字节=1066
    HASH GROUP BY 耗费=839 基数=82 字节=5494
     HASH JOIN 耗费=838 基数=82 字节=5494
      TABLE ACCESS FULL 对象所有者=DBAUSERZON100 对象名称=INF_SHOP 耗费=6 基数=194 字节=4268
      TABLE ACCESS BY INDEX ROWID 对象所有者=DBAUSERZON100 对象名称=CQ_JXC_GYS_DAY 耗费=831 基数=525 字节=23625
       INDEX RANGE SCAN 对象所有者=DBAUSERZON100 对象名称=CQ_JXC_GYS_DAY_IDX 耗费=684 基数=525
   BUFFER SORT 耗费=141075 基数=3074 字节=245920
    VIEW 对象所有者=ZON100 耗费=1644 基数=3074 字节=245920
     HASH GROUP BY 耗费=1644 基数=3074 字节=205958
      TABLE ACCESS BY INDEX ROWID 对象所有者=DBAUSERZON100 对象名称=CQ_JXC_GYS_DAY 耗费=66 基数=16 字节=720
       NESTED LOOPS 耗费=1643 基数=3074 字节=205958
        TABLE ACCESS FULL 对象所有者=DBAUSERZON100 对象名称=INF_SHOP 耗费=6 基数=194 字节=4268
        INDEX RANGE SCAN 对象所有者=DBAUSERZON100 对象名称=CQ_JXC_GYS_DAY_IDX 耗费=4 基数=222

Answer 5

with t as
(SELECT A.*, ROWNUM RN
          FROM (select a.mc, a.spm, a.xssl, b.xssl1, a.kcsl
                  from (select tb.name mc, ta.spm, avg(ta.xssl) xssl, ta.kcsl
                          from CC_JXC_GYS_DAY ta,
                               (select * from inf_shop where fgs = 'C') tb
                         where ta.gys = '10035'
                           and ta.mc = tb.code
                           and ta.rq < add_months(sysdate, -1)
                         group by tb.name, ta.spm, ta.kcsl
                         order by kcsl desc) a,
                       (select tb.name mc,
                               ta.spm,
                               avg(ta.xssl) xssl1,
                               ta.kcsl kcsl
                          from CC_JXC_GYS_DAY ta,
                               (select * from inf_shop where fgs = 'C') tb
                         where ta.gys = '10035'
                           and ta.mc = tb.code
                           and to_char(ta.rq, 'yyyy-mm-dd') =
                               to_char(sysdate - 7, 'yyyy-mm-dd')
                         group by tb.name, ta.spm, ta.kcsl
                         order by kcsl desc) b) a
         WHERE ROWNUM <= 1000)
select * from t where rn>21;

Answer 6

将相同部分用WITH抽取出来试试，估计问题最多的是全表扫描的问题，索引没有加对，
如果不知道怎么处理，可以试试用OEM的SQL ADVICER看看怎么建议的