求高手优化这条sql语句
语句一和语句二都能很快显示结果,但当一和二合并时，却半天没有结果
语句一：
select we.wip_entity_name,
msi.inventory_item_id,
msi.segment1,
we.organization_id,
msi.description,
wdj.START_QUANTITY,
wdj.QUANTITY_completed,
wdj.QUANTITY_SCRAPPED,
wdj.creation_date,
max(WMT.TRANSACTION_DATE) last_date,
wdj.COMPLETION_SUBINVENTORY,
wdj.DATE_COMPLETED,
TO_OPERATION_SEQ_NUM,
wdj.wip_entity_id,
decode(substr(wdj.class_code, 1, 2),
'FX',
msi.attribute1,
msi.WIP_SUPPLY_SUBINVENTORY) WIP_SUPPLY_SUBINVENTORY
from wip.wip_move_transactions wmt,
wip.WIP_DISCRETE_JOBS wdj,
wip.wip_entities we,
inv.mtl_system_items msi
where
wmt.organization_id+0=4
and wdj.wip_entity_id = wmt.wip_entity_id+0
and wdj.COMPLETION_SUBINVENTORY = 'J030半成品'
and wdj.organization_id = wmt.organization_id
and we.wip_entity_id= wmt.wip_entity_id+0
and WMT.TRANSACTION_DATE>= to_date('&p_fm_date','YYMMDD')
and WMT.TRANSACTION_DATE < to_date('&p_to_date','YYMMDD') + 1
and TO_OPERATION_SEQ_NUM =
(select max(OPERATION_SEQ_NUM)
from APPS.WIP_OPERATIONS wo
where wo.wip_entity_id = wmt.wip_entity_id
and wo.organization_id = wmt.organization_id)
and WMT.to_INTRAOPERATION_STEP_TYPE+0= 3
and msi.organization_id= we.organization_id
and msi.inventory_item_id= we.primary_item_id
AND WE.WIP_ENTITY_NAME || '' LIKE '&P_1'
and ((wdj.class_code not like 'FX%' and
msi.WIP_SUPPLY_SUBINVENTORY || '' like '&P_SUB_CODE' || '%') or
(wdj.class_code like 'FX%' and
msi.attribute1 || '' like '&P_SUB_CODE' || '%'))
group by wip_entity_name,
msi.inventory_item_id,
msi.segment1,
we.organization_id,
msi.description,
wdj.START_QUANTITY,
wdj.QUANTITY_completed,
wdj.QUANTITY_SCRAPPED,
wdj.COMPLETION_SUBINVENTORY,
wdj.DATE_COMPLETED,
TO_OPERATION_SEQ_NUM,
wdj.wip_entity_id,
wdj.creation_date,
decode(substr(wdj.class_code, 1, 2),
'FX',
msi.attribute1,
msi.WIP_SUPPLY_SUBINVENTORY)
语句二：
select distinct mmt.transaction_reference
from inv.mtl_material_transactions mmt
where
mmt.organization_id=4
and substr(mmt.transaction_reference,1,2) in('JM','JW')
and mmt.transaction_date >= to_date('&p_fm_date', 'YYMMDD')
and mmt.transaction_date <to_date('&p_to_date', 'YYMMDD') + 1
合并后:
select we.wip_entity_name,
msi.inventory_item_id,
msi.segment1,
we.organization_id,
msi.description,
wdj.START_QUANTITY,
wdj.QUANTITY_completed,
wdj.QUANTITY_SCRAPPED,
wdj.creation_date,
max(WMT.TRANSACTION_DATE) last_date,
wdj.COMPLETION_SUBINVENTORY,
wdj.DATE_COMPLETED,
TO_OPERATION_SEQ_NUM,
wdj.wip_entity_id,
decode(substr(wdj.class_code, 1, 2),
'FX',
msi.attribute1,
msi.WIP_SUPPLY_SUBINVENTORY) WIP_SUPPLY_SUBINVENTORY
from wip.wip_move_transactions wmt,
wip.WIP_DISCRETE_JOBS wdj,
wip.wip_entities we,
--inv.mtl_material_transactions mmt,
inv.mtl_system_items msi
where
wmt.organization_id+0=4
and wdj.wip_entity_id = wmt.wip_entity_id+0
and wdj.COMPLETION_SUBINVENTORY = 'J030半成品'
and wdj.organization_id = wmt.organization_id
and we.wip_entity_id= wmt.wip_entity_id+0
and WMT.TRANSACTION_DATE>= to_date('&p_fm_date','YYMMDD')
and WMT.TRANSACTION_DATE < to_date('&p_to_date','YYMMDD') + 1
and TO_OPERATION_SEQ_NUM =
(select max(OPERATION_SEQ_NUM)
from APPS.WIP_OPERATIONS wo
where wo.wip_entity_id = wmt.wip_entity_id
and wo.organization_id = wmt.organization_id)
and WMT.to_INTRAOPERATION_STEP_TYPE+0= 3
and msi.organization_id= we.organization_id
and msi.inventory_item_id= we.primary_item_id
AND WE.WIP_ENTITY_NAME || '' LIKE '&P_1'
and ((wdj.class_code not like 'FX%' and
msi.WIP_SUPPLY_SUBINVENTORY || '' like '&P_SUB_CODE' || '%') or
(wdj.class_code like 'FX%' and
msi.attribute1 || '' like '&P_SUB_CODE' || '%'))
and we.wip_entity_name not in
(--语句二
select distinct mmt.transaction_reference
from inv.mtl_material_transactions mmt
where
mmt.organization_id=4
and substr(mmt.transaction_reference,1,2) in('JM','JW')
and mmt.transaction_date >= to_date('&p_fm_date', 'YYMMDD')
and mmt.transaction_date <to_date('&p_to_date', 'YYMMDD') + 1
)
group by wip_entity_name,
msi.inventory_item_id,
msi.segment1,
we.organization_id,
msi.description,
wdj.START_QUANTITY,
wdj.QUANTITY_completed,
wdj.QUANTITY_SCRAPPED,
wdj.COMPLETION_SUBINVENTORY,
wdj.DATE_COMPLETED,
TO_OPERATION_SEQ_NUM,
wdj.wip_entity_id,
wdj.creation_date,
decode(substr(wdj.class_code, 1, 2),
'FX',
msi.attribute1,
msi.WIP_SUPPLY_SUBINVENTORY)
[解决办法]
使用存储过程，临时表来做吧！
[解决办法]
--语句一太复杂了，先整个简单的，
/*
AND we.wip_entity_name NOT IN
(--语句二
SELECT DISTINCT mmt.transaction_reference
FROM inv.mtl_material_transactions mmt
WHERE mmt.organization_id =4
AND SUBSTR(mmt.transaction_reference,1,2) IN('JM','JW')
AND mmt.transaction_date >= to_date('&p_fm_date', 'YYMMDD')
AND mmt.transaction_date <to_date('&p_to_date', 'YYMMDD') + 1
)
*/
--1、SUBSTR(mmt.transaction_reference,1,2) IN('JM','JW')可以利用正则表达式来处理一下
--2、用EXISTS替代IN 在一些基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下,使用EXISTS(或NOT EXISTS)通常将提高查询的效率.
--3、需要查询的是一天内的数据，可以试试TRUNC函数，只取日期的年月日 TRUNC(SYSDATE) = 2012-04-17 00:00:00
AND NOT exists (
select 1 from inv.mtl_material_transactions mmt
WHERE mmt.organization_id =4
and regexp_like (mmt.transaction_reference, 'J[MW]+')
AND trunc(mmt.transaction_date)=to_date('&p_fm_date', 'YYYY-MM-DD')
)
/*--附测试例子
select * from (
select 'Jaaaaa' as a from dual
union all
select 'JMbbbbb' from dual
union all
select 'JM' from dual
union all
select 'JWbbbbb' from dual
union all
select 'JW' from dual
union all
select 'JtttMbbbbb' from dual
)
where regexp_like(a, 'J[MW]+')
--结果
A
----------
JMbbbbb
JM
JWbbbbb
JW
*/
[解决办法]
那么长的sql了语句，要转换方式写，不然那么多条件肯定会慢的。
