請求高手SQL優化挑戰（說明：數據庫為8I）

SuperBlade

你们直接在主组织做业务?

yiwenvtech

1. aa不要放在from，不要关联，直接放在第3步之后的结果集查询里面的select子句用子查询某item的相同subinventory和locator的最后进出仓日期
2. transation date > sysdate - 100000等于没有条件，100000等于几乎100年，请结合具体需要使用一个较短的日期范围试试
3. 尝试不使用group by 和having，如果性能有改善，能快速获得结果集，则再使用select在结果集里再加总和过滤

yiwenvtech

你mtl_system_items_b为table full scan, 可尝试使用hint强制使用索引

yinyanhua820727

我現在已經優化成這樣了，可運行了4小時還沒出來
select a.item,
       a.des,
       a.loc,
       a.cla,
       a.sub,
       sum(a.qty),
       a.uom,
       a.t_date2,
       a.factory,
       b.max_date
  from (SELECT /*+rule */
         MSI.SEGMENT1 ITEM,
         MSI.DESCRIPTION DES,
         C.SEGMENT1 LOC,
         mc.segment1 cla,
         mmt.subinventory_code sub,
         mmt.transaction_quantity qty,
         mmt.transaction_uom UOM,
         to_char(MMT.TRANSACTION_DATE, 'yyyy-mm-dd') T_DATE2,
         decode(substr(msi.unit_length, 1, 1),
                1,
                '一厂',
                2,
                '二厂',
                3,
                '三厂',
                6,
                '開發部',
                8,
                '外協',
                '無') factory
          from inv.MTL_MATERIAL_TRANSACTIONS MMT,
               inv.MTL_SYSTEM_ITEMS_B        MSI,
               inv.mtl_item_categories       mic,
               INV.MTL_ITEM_LOCATIONS        C,
               inv.MTL_CATEGORIES_B          MC
         WHERE C.INVENTORY_LOCATION_ID(+) = MMT.LOCATOR_ID
           and mmt.inventory_item_id = MSI.INVENTORY_ITEM_ID
           and mic.inventory_item_id = msi.inventory_item_id
           and mic.category_id = mc.category_id) a,
       ( ----倉庫進倉最後一筆
        select item1, sub1, loc1, max_date
          from (select row_number() over(partition by a.item1, a.sub1, a.loc1 order by a.max_date desc) no,
                        a.item1,
                        a.sub1,
                        a.loc1,
                        a.max_date
                   from (select t.transaction_date  max_date,
                                msb.segment1        item1,
                                t.subinventory_code sub1,
                                mil1.segment1       loc1
                           from inv.mtl_material_transactions t,
                                inv.mtl_system_items_b        msb,
                                inv.mtl_item_locations        mil1
                          where t.inventory_item_id = msb.inventory_item_id
                            and t.locator_id = mil1.inventory_location_id
                            and msb.inventory_item_status_code = 'Active'
                            and t.transaction_quantity > 0) a)
         where no = 1) b
where a.item = b.item1
   and a.sub = b.sub1
   and a.loc = b.loc1
group by a.item,
          a.des,
          a.loc,
          a.cla,
          a.sub,
          a.uom,
          a.t_date2,
          a.factory,
          b.max_date

yinyanhua820727

a子查詢裡面的很快，可b子查詢裡面的改為row_number後還是很慢，大家還有沒有其它好辦法啊，有的話請共享，謝謝！

yinyanhua820727

select item1, sub1, loc1, max_date
  from (select row_number() over(partition by a.item1, a.sub1, a.loc1 order by a.max_date desc) no,
               a.item1,
               a.sub1,
               a.loc1,
               a.max_date
          from (select /*+rule */t.transaction_date  max_date,
                       msb.segment1        item1,
                       t.subinventory_code sub1,
                       mil1.segment1       loc1
                  from inv.mtl_material_transactions t,
                       inv.mtl_system_items_b        msb,
                       inv.mtl_item_locations        mil1
                 where t.inventory_item_id = msb.inventory_item_id
                   and t.locator_id = mil1.inventory_location_id
                   and msb.inventory_item_status_code = 'Active'
                   and t.transaction_quantity > 0) a)
where no = 1
這段怎麼優化啊？

yifei103

可寫procedure 將倉庫最后一筆insert tmp table,然后再join.

yiwenvtech

select max(t.transaction_date)  max_date,
                                t.subinventory_code sub1,
                                mil1.segment1       loc1
                           from inv.mtl_material_transactions t
where organization_id = ?
  and inventory_item_id = ?
and subinventory_code = ?
and locator_id =?

看看这个语句执行的快不快，如果可以的话可以这样取最近进出库日期(子查询或cursor循环)

huchangweiitpub

建议你考虑一下不用MMT table,因为看你就只是抓取库存而已, 你可以考虑一下用MOQ 表