How to improve the performance of the SQL query?

Question

I have Sql Queries where in due to which it's effecting the performance of the package. ln_trans_type_id is the variable declared.

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
);

kindly please review and advise.

Answer 1

If you inside a package declare a variable (as tempVar) and pass later to update

 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

Without an execution plan we can only guess. You should compare the performance with NOT IN but make sure the subquery doesn't return any NULL values, otherwise you won't get any hits.

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
);