SQL调优问题(工作需要救急,急,急,急)

foreverxuqiang 2013-04-24 11:18:44
select d.portfolio_seq, d.security_id, d.trans_date
from inventory_detail d
where exists (
/* 入参为I_PORTFOLIO时。 */
select null from table(PORTFILIO_LIST_SPLIT(I_PORTFOLIO, ','))t
where d.portfolio_seq = t.message
and I_PORTFOLIO <> '-1'
/* 入参为I_GRP_LIST时:普通群组。 */
union all
select c.portfolio_seq from table(PORTFILIO_LIST_SPLIT(I_GRP_LIST, ',')) t1 , inventory_contain c
where c.inv_grp_seq = t1.message
and d.portfolio_seq = c.portfolio_seq
and I_GRP_LIST <> '-1'
/* 入参为I_GRP_LIST时:通用群组。 */
union all
select p.portfolio_seq from portfolio p where not exists ( select null from inventory_contain c where c.portfolio_seq = p.portfolio_seq )
and p.customer_number in( select g.cus_number from inventory_grp g where g.inv_grp_seq in(
select t2.message from table(PORTFILIO_LIST_SPLIT(I_GRP_LIST, ',')) t2 where g.status = 'U'))
and p.portfolio_seq = d.portfolio_seq
and I_GRP_LIST <> '-1')
and d.ivt in ('IVT_BO', 'IVT_OR_S', 'IVT_KR_S')
and d.trans_date >= (select s.business_date from system_data s)
and d.trans_date <= I_TRANS_DATE
and ((d.security_id = I_SECURITY_ID and I_SECURITY_ID <> '-1') or (I_SECURITY_ID = '-1'))
and ((I_MARKET_FLAG = '0' and d.first_trade_date > I_TRANS_DATE) or -- 3.4 区分一级市场券、二级市场券出报表
(I_MARKET_FLAG = '1' and d.first_trade_date <= I_TRANS_DATE) or
I_MARKET_FLAG = '2')

大神们看看这段代码哪里可以进行调优的优化。真的很着急,万分着急。
...全文
118 点赞 收藏 5
写回复
5 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
mgf2012 2013-05-12
直接这样调有点难,水平不够(http://www.meirirm.com)
回复
cyf108 2013-05-10
无参数的子查询可以在外层写成变量或者用游标等
回复
foreverxuqiang 2013-04-24
大神,这个上面发不了太全的东西,可以留个邮箱我发给你吗?
回复
hh7yx 2013-04-24
执行计划呢!!
回复
hh7yx 2013-04-24
去itpub问吧
回复
相关推荐
发帖
Oracle
创建于2007-09-28

1.6w+

社区成员

Oracle开发相关技术讨论
申请成为版主
帖子事件
创建了帖子
2013-04-24 11:18
社区公告
暂无公告