需求:
显示可销天数,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
寻求您的耐心指点。万分感谢。
显示可销天数,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
寻求您的耐心指点。万分感谢。
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;小弟愚见,看看是否有帮助
就是这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
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;
如果不知道怎么处理,可以试试用OEM的SQL ADVICER看看怎么建议的