怎么继续优化读语句-Oracle教程-爱易网页

怎么继续优化读语句

日期：2014-05-17　浏览次数：20945 次

如何继续优化读语句

SQL code


select /*+index(c01d SYS_C003420)*/n5001 门店,n5002 部门,n5004 小分类,n5019 商品编码,c01d21 商品名称,
nvl(xse,0)-nvl(dzxs,0) 销售,
mle 毛利,
nvl(xl,0)-nvl(dzsl,0) 销量
 from 
(select g08,g09,g02,sum(g03*g07) dzxs,sum(G03) dzsl from batchgoods
where g04 between to_date('20100401','yyyymmdd') and to_date('20100430','yyyymmdd')
group by g08,g09,g02),
c01d,(select n5001,n5002,n5004,n5019,sum(n5011) xse,sum(n5016) mle,sum(n5023) xl 
from n50
where n5010 between to_date('20100401','yyyymmdd') and to_date('20100430','yyyymmdd')
group by n5001,n5002,n5004,n5019)
where n5001=g08(+)
and n5019=g02(+)
and n5001=c01d00(+)
and n5019=c01d01(+)

XML code


SELECT STATEMENT, GOAL = CHOOSE            Cost=32884    Cardinality=30832    Bytes=3730672
 MERGE JOIN OUTER            Cost=32884    Cardinality=30832    Bytes=3730672
  SORT JOIN            Cost=32833    Cardinality=30832    Bytes=2651552
   NESTED LOOPS OUTER            Cost=31974    Cardinality=30832    Bytes=2651552
    VIEW    Object owner=FZDC        Cost=1098    Cardinality=30832    Bytes=1880752
     SORT GROUP BY            Cost=1098    Cardinality=30832    Bytes=1171616
      TABLE ACCESS BY INDEX ROWID    Object owner=FZDC    Object name=N50    Cost=668    Cardinality=30832    Bytes=1171616
       INDEX RANGE SCAN    Object owner=FZDC    Object name=IND_N500312_N5010    Cost=86    Cardinality=30832    
    TABLE ACCESS BY INDEX ROWID    Object owner=FZDC    Object name=C01D    Cost=2    Cardinality=1    Bytes=25
     INDEX RANGE SCAN    Object owner=FZDC    Object name=SYS_C003420    Cost=1    Cardinality=1    
  SORT JOIN            Cost=51    Cardinality=1308    Bytes=45780
   VIEW    Object owner=FZDC        Cost=26    Cardinality=1308    Bytes=45780
    SORT GROUP BY            Cost=26    Cardinality=1308    Bytes=62784
     TABLE ACCESS BY INDEX ROWID    Object owner=FZDC    Object name=BATCHGOODS    Cost=6    Cardinality=1308    Bytes=62784
      INDEX RANGE SCAN    Object owner=FZDC    Object name=IND_BATGD2    Cost=2    Cardinality=2355

执行的时间为 12--15 秒之间

------解决方案--------------------
MERGE JOIN OUTER Cost=32884 Cardinality=30832 Bytes=3730672
NESTED LOOPS OUTER Cost=31974 Cardinality=30832 Bytes=2651552
------解决方案--------------------
子查询中各表数据量有多大？查询出的数据量多大？
------解决方案--------------------
你这个结果，共有 30832 条？实际执行返回多少条数据？

用这个试试，并请给出执行计划。

SQL code

select /*+ use_hash(t1 t2 t3)  */
     n5001 门店,
     n5002 部门,
     n5004 小分类,
     n5019 商品编码,
     c01d21 商品名称,
     nvl(xse, 0) - nvl(dzxs, 0) 销售,
     mle 毛利,
     nvl(xl, 0) - nvl(dzsl, 0) 销量
  from (select g08, g09, g02, sum(g03 * g07) dzxs, sum(G03) dzsl
          from batchgoods
         where g04 between to_date('20100401', 'yyyymmdd') 
           and to_date('20100430', 'yyyymmdd')
         group by g08, g09, g02
        ) t1,
       c01d t2,
       (select n5001,
               n5002,
               n5004,
               n5019,
               sum(n5011) xse,
               sum(n5016) mle,
               sum(n5023) xl
          from n50
         where n5010 between to_date('20100401', 'yyyymmdd') 
           and to_date('20100430', 'yyyymmdd')
         group by n5001, n5002, n5004, n5019
       ) t3
 where n5001 = g08(+)
   and n5019 = g02(+)
   and n5001 = c01d00(+)
   and n5019 = c01d01(+)

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

怎么继续优化读语句

相关资料更多>

推荐阅读更多>