如何提高SQL查询的性能？

Question

我有Sql查询，由于它影响了包的性能。ln_trans_type_id是声明的变量。

UPDATE invoice_table xai 
  SET process_flag = 'E', 
      error_description = 'Invoice Number Does not Exists ' 
WHERE xai.process_flag = 'N' 
AND NOT EXISTS (  
    SELECT 1 
    FROM ra_customer_trx_all rct 
    WHERE rct.org_id = 1001 
    AND rct.trx_number = xai.invoice_number 
    AND rct.cust_trx_type_id = ln_trans_type_id
);

敬请审阅并给予建议。

Answer 1

如果您在包中声明一个变量(作为tempVar)，并在稍后传递以更新

 SELECT 1 into tempVar FROM 
 ra_customer_trx_all rct , invoice_table xai  
 WHERE rct.org_id = 1001 AND rct.trx_number = xai.invoice_number AND rct.cust_trx_type_id = ln_trans_type_id

Answer 2

如果没有执行计划，我们只能猜测。您应该将性能与NOT IN进行比较，但要确保子查询不返回任何空值，否则不会得到任何结果。

UPDATE invoice_table xai 
  SET process_flag = 'E', 
      error_description = 'Invoice Number Does not Exists ' 
WHERE xai.process_flag = 'N' 
AND  xai.invoice_number 
           NOT IN (  
    SELECT rct.trx_number 
    FROM ra_customer_trx_all rct 
    WHERE rct.org_id = 2326 
    AND  rct.trx_number is not null -- important!
    AND rct.cust_trx_type_id = ln_trans_type_id
);