为什么用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分钟,还是没出来结果,这是为什么呢???????
以下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分钟,还是没出来结果,这是为什么呢???????
解决方案 »
- PL.SQL.Developer怎么远程连接oracle服务器
- oracle在运行高峰期老是提示存储空间不足,导致新用户连接不上服务器
- oracle update 问题
- 请问如何在package中添加或修改function或存储过程
- Oracle OTN 中文论坛招聘版主
- show sga;后的Fixed Size加上Variable Size,为什么不等于Total System Global Area?
- 在Oracle中删除某条记录时怎样用触发器实现主键(为编号)大于删除的这条所有的编号怎样减1
- oracle9i安装问题
- 在oracle 8i 中如何导出一个数据库的SQL脚本?
- JDeveloper 是不是 Developer 2000的高级版本
- 关于主键的问题
- oracle11g客户端如何链接服务器
消耗的时间就可想而知了