基于临时表的SQL优化
MTL_MATERIAL_TRANSACTIONS的数据量大，消耗的时间7771，837034us 即将近七小时，改写方案如下：1.先创建临时表，用来存放内层结果：
2.将传进来的参数作判断处理，
3.将最外层表与内层结果用hash 返回结果;
结果如下：
CREATE GLOBAL TEMPORARY TABLE CUX.DEAD_MT
(
ORGANIZATION_ID NUMBER,--MSIB_ORGANIZATION_ID
INVENTORY_ITEM_ID NUMBER,--MSIB_INVENTORY_ITEM_ID
item_category VARCHAR2(40 BYTE),-- MCB_SEGMENT1 item_category, --物料大类
item_sub_category VARCHAR2(40 BYTE),-- MCB_SEGMENT2 item_sub_category,--物料小类
SEGMENT1 VARCHAR2(40 BYTE),--MSIB_SEGMENT1
DESCRIPTION VARCHAR2(240 BYTE), --MSIB_DESCRIPTION
PRIMARY_UNIT_OF_MEASUREVARCHAR2(25 BYTE),--MSIB_PRIMARY_UNIT_OF_MEASURE
ITEM_COST NUMBER, --CIC_ITEM_COST
ONHAND_QUANTITY NUMBER, --sum(onhand.transaction_quantity) onhand_quantity,
ONHAND_AMOUNT NUMBER--(cic.item_cost * sum(onhand.transaction_quantity))
)
ON COMMIT DELETE ROWS;
create or replace package body CUX_INV_DEAD_STOCK_DETAIL_PKG is
/* ================================================================================
* PROGRAM NAME:
* CUX_INV_DEAD_STOCK_DETAIL_PKG
*
* ==============================================================================*/
g_error varchar2(500);
g_debug varchar2(500);
procedure outlog(g_message in varchar2) is
begin
fnd_file.PUT_LINE(fnd_file.LOG,g_message);
end outlog; --输出日志
procedure output(g_message in varchar2) is
begin
fnd_file.PUT_LINE(fnd_file.OUTPUT,g_message);
dbms_output.put_line(g_message);
end output; --输出
/* =================================================================================
* FUNCTION / PROCEDURE
* NAME : INV_DEAD_STOCK_DETAIL_MAIN
*
* ==================================================================================*/
procedure inv_dead_stock_detail_main(o_errcode out varchar2,
o_errmess out varchar2,
p_org_id in number, --业务实体
p_item_category in varchar2,--物料类型
p_inventory_item_f in varchar2,--物料从
p_inventory_item_t in varchar2,--物料至
p_dead_days in number --呆滞天数
) is
v_print_date varchar2(30);--打印日期
v_ou varchar2(30);--业务实体
v_item_category varchar2(100); --物料类型
v_inventory_item_f varchar2(100); --物料从
v_inventory_item_t varchar2(100); --物料至
v_dead_days varchar2(10);--呆滞天数
cursor c1 is
select b.organization_id,
b.item_category,
b.item_sub_category,
b.segment1,
b.description,
b.primary_unit_of_measure,
b.item_cost,
b.onhand_quantity,
b.onhand_amount,
b.last_transaction_date,
b.dead_stock_days
from(
select /*+use_hash(mmt ,a)*/
a.ORGANIZATION_ID,
a.item_category,
-- a.INVENTORY_ITEM_ID,
a.item_sub_category,
a.segment1,
a.description,
a.primary_unit_of_measure,
a.item_cost,
a.onhand_quantity,
a.onhand_amount,
max(mmt.transaction_date) last_transaction_date,
trunc(TO_DATE('2015-3-30 11:36:00','YYYY-MM-DD HH24:MI:SS') - max(mmt.transaction_date)) dead_stock_days
--trunc(sysdate - max(mmt.transaction_date)) dead_stock_days
from mtl_material_transactions mmt, DEAD_MT a
where 1 = 1
and a.organization_id = mmt.organization_id
and a.inventory_item_id = mmt.inventory_item_id
and mmt.transaction_type_id 24
and a.item_sub_category=nvl(p_item_category,a.item_sub_category)
--and a.segment1 between nvl(null,a.segment1) and nvl(null,a.segment1)
group by a.organization_id,
a.item_category,
a.item_sub_category,
a.segment1,
a.description,
a.primary_unit_of_measure,
a.item_cost,
a.onhand_quantity,
a.onhand_amount) b
where b.dead_stock_days > nvl(p_dead_days,0);
begin
if (p_inventory_item_f is null and p_inventory_item_t is null)
then
insert /* +append*/intoDEAD_MT
select msib.organization_id,
msib.inventory_item_id,
mcb.segment1item_category, --物料大类
mcb.segment2item_sub_category,--物料小类
msib.segment1,
msib.description,
--onhand.transaction_uom_code,
msib.primary_unit_of_measure,
cic.item_cost,
sum(onhand.transaction_quantity) onhand_quantity,
(cic.item_cost * sum(onhand.transaction_quantity)) onhand_amount
from mtl_system_items_b msib,
cst_item_costs cic,
mtl_categories_b mcb,
mtl_item_categories mic,
mtl_onhand_quantities_detail onhand
where 1=1
and msib.inventory_item_id = onhand.inventory_item_id
and msib.organization_id = onhand.organization_id
and onhand.inventory_item_id = cic.inventory_item_id
and onhand.organization_id = cic.organization_id
and mcb.category_id = mic.category_id
and mic.inventory_item_id=msib.inventory_item_id
and mic.organization_id=msib.organization_id
and mcb.structure_id = 101
and mic.category_set_id = 1
and cic.cost_type_id = 3
and onhand.organization_id = p_org_id
--and msib.inventory_item_id=3073
-- and mcb.segment2=nvl(null,mcb.segment2)and mcb.segment2=nvl(p_item_category,mcb.segment2)
and substr(onhand.subinventory_code,1,1)not in ('E','B')
--and msib.segment1 between nvl(null,msib.segment1) and nvl(null,msib.segment1)
-- and msib.segment1 between nvl(p_inventory_item_f,msib.segment1) and nvl(p_inventory_item_t,msib.segment1)
group by msib.organization_id,
msib.inventory_item_id,
mcb.segment1,
mcb.segment2,
msib.segment1,
msib.description,
msib.primary_unit_of_measure,
cic.item_cost;
end if;
if ( p_inventory_item_f is not null and p_inventory_item_t is not null)
then
insert intoDEAD_MT
select msib.organization_id,
msib.inventory_item_id,
mcb.segment1item_category, --物料大类
mcb.segment2item_sub_category,--物料小类
msib.segment1,
msib.description,
--onhand.transaction_uom_code,
msib.primary_unit_of_measure,
cic.item_cost,
sum(onhand.transaction_quantity) onhand_quantity,
(cic.item_cost * sum(onhand.transaction_quantity)) onhand_amount
from mtl_system_items_b msib,
cst_item_costs cic,
mtl_categories_b mcb,
mtl_item_categories mic,
mtl_onhand_quantities_detail onhand
where 1=1
and msib.inventory_item_id = onhand.inventory_item_id
and msib.organization_id = onhand.organization_id
and onhand.inventory_item_id = cic.inventory_item_id
and onhand.organization_id = cic.organization_id
and mcb.category_id = mic.category_id
and mic.inventory_item_id=msib.inventory_item_id
and mic.organization_id=msib.organization_id
and mcb.structure_id = 101
and mic.category_set_id = 1
and cic.cost_type_id = 3
and onhand.organization_id = p_org_id
--and msib.inventory_item_id=3073
--and mcb.segment2=nvl(null,mcb.segment2)
--and mcb.segment2=p_item_category
and substr(onhand.subinventory_code,1,1)not in ('E','B')
--and msib.segment1 between nvl(null,msib.segment1) and nvl(null,msib.segment1)
and msib.segment1 between p_inventory_item_f and p_inventory_item_t
group by msib.organization_id,
msib.inventory_item_id,
mcb.segment1,
mcb.segment2,
msib.segment1,
msib.description,
msib.primary_unit_of_measure,
cic.item_cost;
end if;
if ( p_inventory_item_f is not null and p_inventory_item_t is null)
then
insert intoDEAD_MT
select msib.organization_id,
msib.inventory_item_id,
mcb.segment1item_category, --物料大类
mcb.segment2item_sub_category,--物料小类
msib.segment1,
msib.description,
--onhand.transaction_uom_code,
msib.primary_unit_of_measure,
cic.item_cost,
sum(onhand.transaction_quantity) onhand_quantity,
(cic.item_cost * sum(onhand.transaction_quantity)) onhand_amount
from mtl_system_items_b msib,
cst_item_costs cic,
mtl_categories_b mcb,
mtl_item_categories mic,
mtl_onhand_quantities_detail onhand
where 1=1
and msib.inventory_item_id = onhand.inventory_item_id
and msib.organization_id = onhand.organization_id
and onhand.inventory_item_id = cic.inventory_item_id
and onhand.organization_id = cic.organization_id
and mcb.category_id = mic.category_id
and mic.inventory_item_id=msib.inventory_item_id
and mic.organization_id=msib.organization_id
and mcb.structure_id = 101
and mic.category_set_id = 1
and cic.cost_type_id = 3
and onhand.organization_id = p_org_id
--and msib.inventory_item_id=3073
--and mcb.segment2=nvl(null,mcb.segment2)
and mcb.segment2=p_item_category
and substr(onhand.subinventory_code,1,1)not in ('E','B')
--and msib.segment1 between nvl(null,msib.segment1) and nvl(null,msib.segment1)
and msib.segment1 between p_inventory_item_f and msib.segment1
group by msib.organization_id,
msib.inventory_item_id,
mcb.segment1,
mcb.segment2,
msib.segment1,
msib.description,
msib.primary_unit_of_measure,
cic.item_cost;
end if;
--处理报表头信息
g_debug := '初始化公司LOGO和输入条件';
--获取打印日期
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')
into v_print_date
from dual;
--获取业务实体
select trim(hou.name)
into v_ou
from hr_operating_units hou
where hou.organization_id = p_org_id;
--获取物料类型
v_item_category:=trim(p_item_category);
--获取物料从
v_inventory_item_f:=trim(p_inventory_item_f);
--获取物料至
v_inventory_item_t:=trim(p_inventory_item_t);
--获取物料呆滞天数
v_dead_days:=to_char(p_dead_days);
--开始输出报表头信息
g_debug :='输出打印日期和用户输入条件';
output('
');
output('' || v_print_date || '');
output('' || v_ou || '');
output('' || v_item_category || '');
output('' || v_inventory_item_f || '');
output('' || v_inventory_item_t || '');
output('' || v_dead_days || '');
--主体数据内循环开始
g_debug :='主体数据内循环开始';
for c1r in c1 loop
output('');
output('' || c1r.item_category ||'.'|| c1r.item_sub_category || '');
output('' || c1r.segment1 || '');
output('' || cux_common_pkg.Xml_Format(c1r.description) || '');
output('' || c1r.primary_unit_of_measure || '');
output('' || c1r.Onhand_Quantity || '');
output('' || to_char(c1r.last_transaction_date,'YYYY-MM-DD HH24:MI:SS') || '');
output('' || c1r.Dead_Stock_Days || '');
output('' || c1r.Item_Cost || '');
output('' || c1r.onhand_amount || '');
output('');
end loop;
output('');
output('');
exception
when others then
g_error := SQLERRM;
INSERT INTO session_log --记录异常信息到异常表
VALUES
(SYSDATE, 'INVENTORY', 'CUX_INV_DEAD_STOCK_DETAIL_PKG', g_debug, g_error);
COMMIT;
outlog('出现错误');
end inv_dead_stock_detail_main;
end CUX_INV_DEAD_STOCK_DETAIL_PKG;
这样变更后，在正式环境中运行的时为3分钟46秒，测试环境，从7小时变是20分钟。
页:
[1]