oracle distict 效率,为什么用distinct时很慢?解决方法

当前位置:我的异常网» Oracle管理 » 为什么用distinct时很慢?解决方法

为什么用distinct时很慢?解决方法

www.myexceptions.net  网友分享于：2013-05-09  浏览：451次

为什么用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,

文章评论