MTL_ONHAND_QUANTITIES_DETAIL索引如下:i1:INVENTORY_ITEM_ID, ORGANIZATION_ID, SUBINVENTORY_CODE, PRIMARY_TRANSACTION_QUANTITYi2:ORGANIZATION_ID, SUBINVENTORY_CODE, LOCATOR_ID, LPN_IDi3:ORGANIZATION_ID, INVENTORY_ITEM_ID, COST_GROUP_ID
我现在关联其它表用到 INVENTORY_ITEM_ID(i1), (ORGANIZATION_ID, SUBINVENTORY_CODE)(i1、i2共有), LOCATOR_ID(i2)这几个字段查询,但是跟踪了一下好像没有用到索引,是全表扫描的,请问该咋整,我是否该再建一个复合索引?
我现在关联其它表用到 INVENTORY_ITEM_ID(i1), (ORGANIZATION_ID, SUBINVENTORY_CODE)(i1、i2共有), LOCATOR_ID(i2)这几个字段查询,但是跟踪了一下好像没有用到索引,是全表扫描的,请问该咋整,我是否该再建一个复合索引?
ood.ORGANIZATION_CODE Org_Code,
msi.segment1 Item_Code,
msi.description Item_Desc,
msi.primary_unit_of_measure Item_Unit,
decode(substr(mc.SEGMENT1, 1, 1), '1', '产成品', '非产成品') CAT,
moq.SUBINVENTORY_CODE CK,
mil.segment1 CW,
NVL(SUM(MOQ.TRANSACTION_QUANTITY), 0) on_hand,
NVL(SUM(MOQ.TRANSACTION_QUANTITY), 0) -
(SELECT NVL(SUM(MR.RESERVATION_QUANTITY), 0)
FROM MTL_RESERVATIONS MR
WHERE MR.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MR.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MR.SUBINVENTORY_CODE = moq.SUBINVENTORY_CODE
and nvl(mr.locator_id, -1) = nvl(moq.LOCATOR_ID, -1)) -
(SELECT NVL(SUM(MI.MSI_SCAN_QTY), 0)
FROM MES_SALES MS, MES_SALES_ITEM MI
WHERE MS.MS_STATUS <> 3
AND MS.MS_ID = MI.MS_ID
AND MI.MSI_ITEM = MSI.SEGMENT1
AND MS.MS_ORG_ID = MSI.ORGANIZATION_ID
AND MS.MS_STORE_CODE = moq.SUBINVENTORY_CODE
/*AND nvl(MS.MS_CW,'-1') = nvl(MIL.SEGMENT1,'-1')*/) QTY from mtl_system_items_b msi,
mtl_onhand_quantities moq,
mtl_item_locations mil,
mtl_item_categories_v mc,
org_organization_definitions ood
where 1 = 1
and msi.organization_id = moq.ORGANIZATION_ID
and msi.inventory_item_id = moq.INVENTORY_ITEM_ID
and moq.LOCATOR_ID = mil.inventory_location_id(+)
and moq.ORGANIZATION_ID = mil.organization_id(+)
and moq.SUBINVENTORY_CODE = mil.subinventory_code(+)
and msi.organization_id = mc.ORGANIZATION_ID
and msi.inventory_item_id = mc.INVENTORY_ITEM_ID
and moq.ORGANIZATION_ID = ood.ORGANIZATION_ID
and MC.CATEGORY_SET_ID=1
/* and moq.SUBINVENTORY_CODE='361'
and moq.ORGANIZATION_ID=153*/
group by moq.ORGANIZATION_ID,
ood.ORGANIZATION_CODE,
msi.segment1,
MSI.INVENTORY_ITEM_ID,
msi.primary_unit_of_measure,
MSI.ORGANIZATION_ID,
mc.SEGMENT1,
msi.description,
moq.SUBINVENTORY_CODE,
mil.segment1,
moq.LOCATOR_ID