Identify deleted records

Database

Identify deleted records

I need help in optimizing the query below:
Query is about identifying UK of deletes in a large table (70+ GB) using the pre-load-image and post-load-image. Here is the query we have written but this consume a ton of CPU.

The two tables are joined on the UK in the query below:

Need urgent help for performance improvement or any other alternatives, immediate responses would be greatly appreciated.

LOCK TABLE pre_load_image FOR access
LOCK TABLE post_load_image FOR access
SELECT 'sap_clnt_id=' || COALESCE( A.sap_clnt_id , '' ) || '~co_cd=' || COALESCE( A.co_cd , '' ) || '~fin_doc_nbr=' || COALESCE( A.fin_doc_nbr , '' ) || '~fscl_yr_nbr=' || COALESCE( A.fscl_yr_nbr , '' ) || '~fin_doc_line_nbr=' || COALESCE( A.fin_doc_line_nbr , '' )
FROM pre_load_image A
WHERE NOT EXISTS (
SELECT sap_clnt_id , co_cd , fin_doc_nbr , fscl_yr_nbr
FROM post_load_image B
WHERE A.sap_clnt_id = B.sap_clnt_id
AND A.co_cd = B.co_cd
AND a.fin_doc_nbr = b.fin_doc_nbr
AND A.fscl_yr_nbr = b.fscl_yr_nbr
AND A.fin_doc_line_nbr = b.fin_doc_line_nbr ) ;

Thanks in advance!
_Sree
1 REPLY
N/A

Re: Identify deleted records

The NOT EXISTS form of your Query is probably processed as a correlated subquery. Some of them are known to consume a lot of time and resource.

Try rewriting your query using the NOT IN form.

LOCKING ROW FOR ACCESS
SELECT 'sap_clnt_id=' || COALESCE( A.sap_clnt_id , '' ) || '~co_cd=' || COALESCE( A.co_cd , '' ) || '~fin_doc_nbr=' || COALESCE( A.fin_doc_nbr , '' ) || '~fscl_yr_nbr=' || COALESCE( A.fscl_yr_nbr , '' ) || '~fin_doc_line_nbr=' || COALESCE( A.fin_doc_line_nbr , '' )
FROM pre_load_image A
WHERE (sap_clnt_id, co_cd, fin_doc_nbr, fscl_yr_nbr,fin_doc_line_nbr)
NOT IN (SELECT sap_clnt_id, co_cd, fin_doc_nbr, fscl_yr_nbr,fin_doc_line_nbr
FROM post_load_image B);

PS. Please test the queries on test tables with similar data. I think the results may vary between the NOT EXISTS and NOT IN forms if the columns used in the condition are NULLABLE.