SQL调优问题(工作需要救急,急,急,急)
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'))