question

artisltover avatar image
artisltover asked

I have some code written prior to my joining the team. It uses a lot of union alls and is hanging when inserting.

INSERT INTO ef_extract_codes_t SELECT coa_key, extract_code1 FROM ef_load_it l WHERE extract_code1 IS NOT NULL AND NOT EXISTS (SELECT e.coa_key||e.extract_code FROM ef_extract_codes_t e WHERE e.coa_key||e.extract_code = l.coa_key||extract_code1) UNION ALL SELECT coa_key, extract_code2 FROM ef_load_it l WHERE extract_code2 IS NOT NULL AND NOT EXISTS (SELECT e.coa_key||e.extract_code FROM ef_extract_codes_t e WHERE e.coa_key||e.extract_code = l.coa_key||extract_code2) UNION ALL SELECT coa_key, extract_code3 FROM ef_load_it l WHERE extract_code3 IS NOT NULL AND NOT EXISTS (SELECT e.coa_key||e.extract_code FROM ef_extract_codes_t e WHERE e.coa_key||e.extract_code = l.coa_key||extract_code3) UNION ALL SELECT coa_key, extract_code4 FROM ef_load_it l WHERE extract_code4 IS NOT NULL AND NOT EXISTS (SELECT e.coa_key||e.extract_code FROM ef_extract_codes_t e WHERE e.coa_key||e.extract_code = l.coa_key||extract_code4) UNION ALL SELECT coa_key, extract_code5 FROM ef_load_it l WHERE extract_code5 IS NOT NULL AND NOT EXISTS (SELECT e.coa_key||e.extract_code FROM ef_extract_codes_t e WHERE e.coa_key||e.extract_code = l.coa_key||extract_code5) UNION ALL SELECT coa_key, extract_code6 FROM ef_load_it l WHERE extract_code6 IS NOT NULL AND NOT EXISTS (SELECT e.coa_key||e.extract_code FROM ef_extract_codes_t e WHERE e.coa_key||e.extract_code = l.coa_key||extract_code6) UNION ALL SELECT coa_key, extract_code7 FROM ef_load_it l WHERE extract_code7 IS NOT NULL AND NOT EXISTS (SELECT e.coa_key||e.extract_code FROM ef_extract_codes_t e WHERE e.coa_key||e.extract_code = l.coa_key||extract_code7);

union
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.