请帮忙优化一段sql语句

billlyh 2012-10-25 04:47:39

select we.wip_entity_name
,msi1.segment1
,msi1.description
,wdj.START_QUANTITY,wdj.QUANTITY_COMPLETED
,DECODE(STATUS_TYPE,1,'未发放',--3,'已发放',
4,'已完成',6,'暂挂',7,'已取消',12,'已关闭',14,'待定关闭',15,'无法关闭',
to_char(STATUS_TYPE)) STATUS
,msi2.segment1 segment2
,msi2.description description2
,SUPPLY_SUBINVENTORY
,wro.REQUIRED_QUANTITY
,wro.QUANTITY_ISSUED
,wro.DATE_REQUIRED
,WRO.OPERATION_SEQ_NUM
,WRO.ATTRIBUTE2
,CLASS_CODE,MINV.ATTRIBUTE2
from
inv.mtl_system_items msi1
,inv.mtl_system_items msi2
,wip.wip_requirement_operations wro
,wip.wip_discrete_jobs wdj
,wip.wip_entities we
,inv.mtl_secondary_inventories minv
where msi2.organization_id=4
and wro.organization_id=msi2.organization_id
and msi1.organization_id=msi2.organization_id
and wdj.organization_id=msi2.organization_id
and we.organization_id=msi2.organization_id

and wro.wip_entity_id=wdj.wip_entity_id
and wdj.PRIMARY_ITEM_ID=msi1.inventory_item_id
and wro.inventory_item_id=msi2.inventory_item_id
and wro.WIP_SUPPLY_TYPE in (1,3)
and we.wip_entity_id=wdj.wip_entity_id
and wro.QUANTITY_ISSUED<>wro.REQUIRED_QUANTITY
and we.wip_entity_name not like 'X%'
and we.wip_entity_name not like 'x%'
AND MINV.ORGANIZATION_ID=4 AND WRO.SUPPLY_SUBINVENTORY=MINV.SECONDARY_INVENTORY_NAME
and ( '&P_STATUS'='全部' and (STATUS_TYPE+0=4 or STATUS_TYPE+0=12) --OR ('&P_STATUS'='已发放' AND STATUS_TYPE+0=3)
OR ('&P_STATUS'='已完成' AND STATUS_TYPE+0=4)
OR ('&P_STATUS'='已关闭' AND STATUS_TYPE+0=12) )
and ('&P_CLASS'='全部' and (CLASS_CODE='JJStandard' or CLASS_CODE='JJNonSTD' or CLASS_CODE='FXJJNonStd' or CLASS_CODE='FXJJStd')
OR ('&P_CLASS'<>'全部' and CLASS_CODE||''='&P_CLASS')
)
------------------------------------------------以下为出问题语句
and
(
----------------------条件1
(
msi2.segment1 like '&P_ITEM'
and '&P_DATEF' is null
and '&P_DATET' is null

)
----------------------条件2
or
(
'&P_ITEM' is null
and wro.DATE_REQUIRED>=to_date('&P_DATEF','yyyymmdd')
and wro.DATE_REQUIRED<to_date('&P_DATET','yyyymmdd')+1
)
----------------------条件3
or
(
msi2.segment1 like '&P_ITEM'
and wro.DATE_REQUIRED>=to_date('&P_DATEF','yyyymmdd')
and wro.DATE_REQUIRED<to_date('&P_DATET','yyyymmdd')+1
)
)
---------------------------------------------------------------
order by wro.DATE_REQUIRED
上面语句当三个条件同时有时，半天没出数据，
而当只取其中一个条件，另两个屏蔽，例如只取条件1,条件2、条件3屏蔽，则查询时很快有结果
为什么会这样呢，哪位兄弟帮帮忙分析，优化一下

...全文