这段SQL为什么不走索引?

[rfb0204421]

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,求救.

[andornot123]

你的索引字段作为条件查询了吗?
试下把索引字段作为条件查询下.

[rfb0204421]

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
这三个查询条件都是索引字段啊

[rfb0204421]

SELECT STATEMENT, GOAL = ALL_ROWS Cost=105919 Cardinality=1 Bytes=121
HASH GROUP BY Cost=105919 Cardinality=1 Bytes=121
FILTER
NESTED LOOPS Cost=105918 Cardinality=1 Bytes=121
NESTED LOOPS Cost=105470 Cardinality=228 Bytes=22344
NESTED LOOPS Cost=104334 Cardinality=449 Bytes=28287
TABLE ACCESS FULL Object owner=WIP Object name=WIP_DISCRETE_JOBS Cost=5230 Cardinality=3875 Bytes=77500
TABLE ACCESS BY INDEX ROWID Object owner=INV Object name=MTL_MATERIAL_TRANSACTIONS Cost=93 Cardinality=1 Bytes=43
INDEX RANGE SCAN Object owner=INV Object name=MTL_MATERIAL_TRANSACTIONS_N1 Cost=3 Cardinality=142
TABLE ACCESS BY INDEX ROWID Object owner=INV Object name=MTL_TRANSACTION_LOT_NUMBERS Cost=3 Cardinality=1 Bytes=35
INDEX RANGE SCAN Object owner=INV Object name=MTL_TRANSACTION_LOT_NUMBERS_N1 Cost=2 Cardinality=1
TABLE ACCESS BY INDEX ROWID Object owner=WIP Object name=WIP_ENTITIES Cost=2 Cardinality=1 Bytes=23
INDEX UNIQUE SCAN Object owner=WIP Object name=WIP_ENTITIES_U1 Cost=1 Cardinality=1
TABLE ACCESS BY INDEX ROWID Object owner=INV Object name=MTL_SECONDARY_INVENTORIES Cost=2 Cardinality=1 Bytes=15
INDEX UNIQUE SCAN Object owner=INV Object name=MTL_SECONDARY_INVENTORIES_U1 Cost=1 Cardinality=1
执行计划帖出来大家看哈

[iqlife]

TABLE ACCESS FULL Object owner=WIP Object name=WIP_DISCRETE_JOBS Cost=5230 Cardinality=3875 Bytes=77500

明显的走了全表扫描

[yinan9]

[Quote=引用 9 楼 java3344520 的回复:]

TABLE ACCESS FULL Object owner=WIP Object name=WIP_DISCRETE_JOBS Cost=5230 Cardinality=3875 Bytes=77500

明显的走了全表扫描
[/Quote]权威来了

[x_smooth]

现在有个问题：楼主现在用没有走索引的语句 导致执行效率变差了，还是说没走索引不影响速度但想知道原因？
不是说走了索引速度就快

[rfb0204421]

有人吗

[yinan9]

AND wdj.attribute10 is not null
AND wdj.attribute11 is not null
在进行NULL或者NOT NULL判断的时候，会进行全表检索，不会走索引
可以适当地在此做一点优化

[rfb0204421]

我去掉这两个条件,还是没有走索引的.并且我单独查这个表,会走索引.

[Rotel-刘志东]

具体看一下执行的计划为什么没有走索引的。或者跟踪一下语句。

[rfb0204421]

小弟只会看执行计划没有走索引,至于为什么没走,就看不出来了,请指点一二

[rfb0204421]

现在这个查询非常慢,我怀疑是没有走索引的原因,所以.....

[yinan9]

本身这段SQL就包含统计分组函数，结构也些许复杂，
执行笑傲率应该不会太快的