这段SQL为什么不走索引?
SELECT TO_CHAR (mmt.transaction_date, 'yyyy/mm/dd') AS transaction_date,
wdj.attribute10 AS order_number,
wdj.attribute11 AS order_line_number, mmt.organization_id,
mmt.inventory_item_id,
-SUM (mtl.transaction_quantity) AS transaction_quantity,
mmt.transaction_uom, 1 trx_type
FROM mtl_material_transactions mmt,
mtl_transaction_lot_numbers mtl,
wip_discrete_jobs wdj,
wip_entities we
WHERE 1 = 1
AND mmt.organization_id = mtl.organization_id
AND mmt.transaction_id = mtl.transaction_id
AND mmt.inventory_item_id = mtl.inventory_item_id
AND wdj.wip_entity_id = we.wip_entity_id
AND wdj.organization_id = mmt.organization_id
AND wdj.primary_item_id = mmt.inventory_item_id
AND SUBSTR (mtl.lot_number,
INSTR (mtl.lot_number, '#', 1) + 1,
LENGTH (mtl.lot_number)
) = we.wip_entity_name
AND mmt.transaction_source_type_id = 13
AND mmt.transaction_action_id = 2
AND ( mmt.subinventory_code IN ('QC90', 'QC91', 'QC92')
OR mmt.subinventory_code IN (
SELECT DISTINCT msub.secondary_inventory_name
FROM mtl_secondary_inventories msub
WHERE NVL (msub.attribute2, 'abc') = 'PRD'
AND msub.organization_id = mmt.organization_id)
)
AND wdj.attribute10 is not null
AND wdj.attribute11 is not null
AND mmt.transfer_subinventory LIKE '%88'
GROUP BY TO_CHAR (mmt.transaction_date, 'yyyy/mm/dd'),
wdj.attribute10,
wdj.attribute11,
mmt.organization_id,
mmt.inventory_item_id,
mmt.transaction_uom
各位大虾,这段SQL中的wip_discrete_jobs wdj表没有走索引,不知道为什么,其它几个表都走了,此表有49W数据,
建有联合索引(organization_id,wip_entity_id),单独索引primary_item_id,求救.
[解决办法]
AND wdj.attribute10 is not null
AND wdj.attribute11 is not null
在进行NULL或者NOT NULL判断的时候，会进行全表检索，不会走索引
可以适当地在此做一点优化
[解决办法]
具体看一下执行的计划为什么没有走索引的。或者跟踪一下语句。
[解决办法]
你的索引字段作为条件查询了吗?
试下把索引字段作为条件查询下.
[解决办法]
TABLE ACCESS FULLObject owner=WIPObject name=WIP_DISCRETE_JOBSCost=5230Cardinality=3875Bytes=77500
明显的走了全表扫描
[解决办法]
