日期:2014-05-16  浏览次数:20832 次

求高手优化这条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_i