为什么用distinct时很慢?
为什么用distinct时很慢?
以下select语句很快就得到结果，共有十几万行，但是有很多重复行，
select 
  
  J030.* from
  (  
  select 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,
  mmt.TRANSACTION_REFERENCE REFERENCE,  
  mmt.transaction_type_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 
  --TRANSACTION_DATE >= nvl(:p_fm_date, to_date('20000101', 'yyyymmdd'))
  --and TRANSACTION_DATE < nvl(:p_to_date, to_date('20200101', 'yyyymmdd')) + 1
  WMT.TRANSACTION_DATE >= nvl(to_date('&p_fm_date','YYMMDD'), to_date('20000101', 'yyyymmdd'))
  and WMT.TRANSACTION_DATE < nvl(to_date('&p_to_date','YYMMDD'), to_date('20200101', 'yyyymmdd')) + 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 to_INTRAOPERATION_STEP_TYPE = 3
  and wdj.wip_entity_id = wmt.wip_entity_id
  and wdj.COMPLETION_SUBINVENTORY = 'J030半成品'
  and wdj.organization_id = wmt.organization_id
  and we.wip_entity_id = wmt.wip_entity_id
  --AND we.wip_entity_name not like '%W%'
  --AND WE.WIP_ENTITY_NAME || '' LIKE :P_1
  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 ((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 msi.inventory_item_id = we.primary_item_id
  and msi.organization_id = we.organization_id
   
   
  --and mmt.inventory_item_id=msi.inventory_item_id
  and mmt.inventory_item_id=we.primary_item_id
  and mmt.organization_id=we.organization_id
  and SUBINVENTORY_code||'' ='J030半成品' 
  and mmt.transaction_date>=we.creation_date 
  and mmt.TRANSACTION_SOURCE_ID=we.wip_entity_id
  --and mmt.transaction_type_id not in (128)  
   
  and we.wip_entity_name like '%JM%'
  group by wip_entity_name,
  msi.inventory