为什么用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_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,
mmt.TRANSACTION_REFERENCE,
mmt.transaction_type_id,
decode(substr(wdj.class_code, 1, 2),
'FX',
msi.attribute1,
msi.WIP_SUPPLY_SUBINVENTORY)
)J030,
(
select we1.wip_entity_name
from inv.mtl_material_transactions mmt1, wip.wip_entities we1
where mmt1.organization_id = 4
--and mmt1.inventory_item_id = 230797
and mmt1.transaction_type_id in(128)
and mmt1.inventory_item_id = we1.primary_item_id
and we1.organization_id = 4
--and we1.wip_entity_name = 'JM9R0105001'
and mmt1.transaction_date>=to_date('&p_fm_date','YYMMDD')
and mmt1.transaction_date<to_date('&p_to_date','YYMMDD')
--AND we1.wip_entity_name like 'J%')
)WM
where J030.wip_entity_name<>WM.wip_entity_name
为了筛选不重复数据，加了distinct
运行了40分钟，还是没出来结果，这是为什么呢？？？？？？？
[解决办法]
方法一：给这个表加上索引。
方法二：做个作业每天提前执行存储到结果表里面，第二天直接查结果。
[解决办法]
运行机制不同 少量数据没什么影响 但是大量数据 尽量避免使用distinct
[解决办法]
用group by 也行啊 在效率上完胜distinct。。
