如何优化一段sql
zdwip_slip_headers、zdwip_slip_lines表是自定义的,
以下语句很快就有结果，但一连接这两个自定义表就很慢，要一个多小时才出来，哪位帮忙优化一下呢??????
select we.wip_entity_name,wro.wip_entity_id,
msi.segment1 segment1,msi.description description1,
msi2.segment1 segment2,wro.inventory_item_id,msi2.description description2,
wdj.status_type,
wdj.date_completed,
wdj.date_closed,
wdj.class_code,
wro.required_quantity,
wro.quantity_issued,
wro.required_quantity-wro.quantity_issued quantity_OPEN,msi2.primary_uom_code,
wro.supply_subinventory,
WRO.ATTRIBUTE2 BZ,
WRO.OPERATION_SEQ_NUM
from inv.mtl_system_items msi,
inv.mtl_system_items msi2,
--sdcd.zdwip_slip_headers zsh,sdcd.zdwip_slip_lines zsl,
wip.wip_entities we,
wip.wip_discrete_jobs wdj,
wip.wip_requirement_operations wro
where wdj.organization_id = 4
and we.organization_id = 4
and wro.organization_id = 4
and msi.organization_id=4 and msi2.organization_id=4
and we.wip_entity_id = wdj.wip_entity_id
and wro.wip_entity_id = wdj.wip_entity_id
/* and zsh.slip_id=zsl.slip_header_id
and zsl.wip_entity_id(+)=wro.wip_entity_id and zsl.inventory_item_id(+)=wro.inventory_item_id
and zsl.operation_seq_num(+)=wro.operation_seq_num*/
and wdj.primary_item_id=msi.inventory_item_id and wro.inventory_item_id=msi2.inventory_item_id
and wro.required_quantity<>0
and wro.required_quantity <> wro.quantity_issued
and (('&P_NO_KJ'='Y' AND WRO.SUPPLY_SUBINVENTORY not like '%开架%') OR '&P_NO_KJ'='N')
and ('&P_CLASS'='全部' OR ('&P_CLASS'<>'全部' AND CLASS_CODE||''='&P_CLASS'))
and
(
(
'&P_STATUS'='已完成' and wdj.status_type = 4
and wdj.DATE_COMPLETED >= to_date('&P_DATE_F', 'yymmdd')
and wdj.DATE_COMPLETED <= to_date('&P_DATE_T', 'yymmdd')
)
or
(
'&P_STATUS'='已关闭' and wdj.status_type = 12
and nvl(wdj.date_closed,to_date('&P_DATE_F', 'yymmdd')) >= to_date('&P_DATE_F', 'yymmdd')
and nvl(wdj.date_closed,to_date('&P_DATE_F', 'yymmdd')) <= to_date('&P_DATE_T', 'yymmdd')
)
)
[解决办法]
如果速度下降太多，就使用过程，分别用多个SQL来完成，不一定非用一个SQL。
[解决办法]
or 换成 union ，前者是执行一条语句，复杂度高，慢；后者，换成两条语句，复杂度简化，快
