sql执行计划计划有的奇怪

显示全部楼层 · 2008-1-2 17:35:53

SELECT MAX (y.oms_transaction_id)
FROM oms_inv_doc_transaction y
WHERE y.inventory_item_id = :b4
AND y.store_need_flag = 'Y'
AND y.organization_id = :b3
AND y.transaction_date <= :b2
AND y.oms_transaction_id < NVL (:b1, oms_transaction_id + 1)
执行计划是
SELECT STATEMENT Optimizer Mode=CHOOSE
1
4

SORT AGGREGATE
1
21

CONCATENATION

FILTER

TABLE ACCESS BY INDEX ROWID
OMS.OMS_INV_DOC_TRANSACTION
1
21
2

INDEX RANGE SCAN
OMS.OMS_INV_DOC_TRANSACTION_N2
3
1

FILTER

TABLE ACCESS BY INDEX ROWID
OMS.OMS_INV_DOC_TRANSACTION
1
21
2

INDEX RANGE SCAN
OMS.OMS_INV_DOC_TRANSACTION_N2
3
1
其中OMS.OMS_INV_DOC_TRANSACTION_N2是oms_inv_doc_transaction (inventory_item_id )的索引。

去掉最后一个条件，后
SELECT MAX (y.oms_transaction_id)
FROM oms_inv_doc_transaction y
WHERE y.inventory_item_id = :b4
AND y.store_need_flag = 'Y'
AND y.organization_id = :b3
AND y.transaction_date <= :b2
-- AND y.oms_transaction_id < NVL (:b1, oms_transaction_id + 1)
执行计划正常了，

Operation
Object Name
Rows
Bytes
Cost
Object Node
In/Out
PStart
PStop
SELECT STATEMENT Optimizer Mode=CHOOSE
1
2

SORT AGGREGATE
1
21

TABLE ACCESS BY INDEX ROWID
OMS.OMS_INV_DOC_TRANSACTION
1
21
2

INDEX RANGE SCAN
OMS.OMS_INV_DOC_TRANSACTION_N2
3
1

怎么解释前一个执行计划？

千问 · 2008-1-2 17:35:53

如果b1 = null
, 第一个SQL存在一个循环比较。