原本我们的SQL 是这么写的:
SELECT
/*+ INDEX (rts RCV_TRANSACTIONS_N16) */
...
FROM ...
WHERE ...
AND rts.source_document_code = 'PO'
AND nvl(rts.invoice_status_code,'NA') IN ('PENDING','REJECTED')
AND rts.transaction_type = X_receipt_event
这么写是没有问题的, INDEX RCV_TRANSACTIONS_N16 是为这3个字段建的索引:
PO RCV_TRANSACTIONS_N16 NONUNIQUE VALID FUNCTION-BASED NORMAL
SOURCE_DOCUMENT_CODE TRANSACTION_TYPE NVL("INVOICE_STATUS_CODE",'NA')
这样就会先通过这个索引查询出数据, 再用其他条件过滤.
但是后来因为一个其他bug, 把查询条件改了, 加了一些其他条件:
AND rts.source_document_code = 'PO'
AND nvl(rts.invoice_status_code,'NA') IN ('PENDING','REJECTED')
AND ((rts.transaction_type = X_receipt_event)
OR (X_include_timecard_correct = 'Y'
AND rts.transaction_type = 'CORRECT'
AND rts.timecard_id is not null
AND exists(select 1
from rcv_transactions prt
where prt.transaction_type = 'RECEIVE'
and prt.SOURCE_DOCUMENT_CODE = 'PO'
and prt.transaction_id = rts.parent_transaction_id
and prt.invoice_status_code ='INVOICED')))
里面多了子查询. 这样再用这个索引查数据的时候, 性能变得极差.
具体怎么改还没想好, 不过子查询应该是不用了. 需要改一下然后测试执行计划是不是正常.