基于临时表的SQL优化

shuijingping

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_MEASURE  VARCHAR2(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*/into  DEAD_MT
　　
select msib.organization_id,
　　
                            msib.inventory_item_id,
　　
                            mcb.segment1  item_category,     --物料大类
　　
                            mcb.segment2  item_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 into  DEAD_MT
　　
select msib.organization_id,
　　
                            msib.inventory_item_id,
　　
                            mcb.segment1  item_category,     --物料大类
　　
                            mcb.segment2  item_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 into  DEAD_MT
　　
select msib.organization_id,
　　
                            msib.inventory_item_id,
　　
                            mcb.segment1  item_category,     --物料大类
　　
                            mcb.segment2  item_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分钟。