oracle SQL优化,吐槽点拨都欢迎,也许就有灵感了

sxgkwei 发布于 2012/08/10 18:49
阅读 194
收藏 0
select k.productid,
                       k.productname,
                       k.dcInventory,
                       sum(k.storeauditqty),
                       k.dcName,
                       k.productstatus_Id,
                       avg(k.weekDaySaleQuantity),
                       avg(k.monthDaySaleQuantity),
                       sum(k.inventoryVolume),
                       sum(k.recommendQuantity),
                       sum(k.storeQuantity),
                       sum(k.regionQuantity),
                       sum(k.lineTotalPrice)
                  from (select dc.InventoryVolume as dcInventory,
                               t.*,
                               b.userid           as ba_userid
                          from T_BIZ_STORESALESTOCK_Replenish dc,
                               t_biz_replenishorderdetail     t,
                               t_biz_userdatagroup            b
                         where b.leveloneid = t.leveloneid
                           and b.leveltwoid = '0'
                           and t.status in ('2', '1', '0')
                           and b.userid = '62637'
                           and t.regionid = 'QSZHS'
                           and dc.orgid = t.dcid
                           and dc.productid = t.productid
                           and dc.orgid like 'D%'
                        union all
                        select dc.InventoryVolume as dcInventory,
                               t.*,
                               b.userid           as ba_userid
                          from T_BIZ_STORESALESTOCK_Replenish dc,
                               t_biz_replenishorderdetail     t,
                               t_biz_userdatagroup            b
                         where b.leveltwoid = t.leveltwoid
                           and b.leveltwoid > '0'
                           and t.status in ('2', '1', '0')
                           and b.userid = '62637'
                           and t.regionid = 'QSZHS'
                           and dc.orgid = t.dcid
                           and dc.productid = t.productid
                           and dc.orgid like 'D%') k
                 group by k.productid,
                          k.productname,
                          k.productstatus_Id,
                          k.dcInventory,

                          k.dcName

大家都支招,看看怎么优化下,现在运行太慢了。@宏哥 可是高手,怎么能不at呢?嘿嘿

加载中
0
h
hongpeng

t.*如果列很多要修改下,只选择你要的列。

FROM中的两个SQL语句可以合并成一个,可以用DISTINCT (and b.leveltwoid > '0'是不是就这个条件不一样),没有必要Union all 

建立合适的索引。因为你这有常量值

sxgkwei
sxgkwei
恩,大多数按照你说的做了,现在基本达到速度要求了。不过union all还是在用,因为一个是b.leveltwoid = t.leveltwoid ,一个是b.leveloneid = t.leveloneid ,分情况的。所以。。。
0
宏哥
宏哥

一点点提示, 

 在交易表上先形成结果集, 做子查询,速度自然就快

太多笛卡尔乘积了,要减小笛卡尔乘积规模.

优化的首要任务就是 在逻辑上减小乘积规模.

sxgkwei
sxgkwei
没有看到哪儿有笛卡尔积啊。。。不懂,再指点下。
0
IdleMan
IdleMan
都不给个什么查询计划的怎么看
0
猫哥-u
猫哥-u
执行计划能贴出来吗
返回顶部
顶部