一个SQL 查询的性能问题

来源：互联网发布：淘宝鲁班系统编辑：程序博客网时间：2024/04/18 20:45

原本我们的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')))

里面多了子查询. 这样再用这个索引查数据的时候, 性能变得极差.
具体怎么改还没想好, 不过子查询应该是不用了. 需要改一下然后测试执行计划是不是正常.

0 0