UNION clause performance issue with sql

Tags: sql oracle union

I have the below union clause causing the overall stored procedure to run very slowly. I am using union to collect information from the same table, but the second and third union clauses join to other tables too for filtering. I understand this may not be the best coding approach and was hoping someone could guide me on a better syntax.

select ss.int_tran_id 
from status ss 
where ss.stage in ('ACHPayment_Confirmed', 'HIFV4', 'HIFV5_FTRINF', 'Payment_HIFV5_FTRINF')
select ss.int_tran_id 
from status ss, references rf
where ss.int_tran_id = rf.int_tran_id
and ss.stage = 'PREVDAY'
and rf.mid_ref IS NOT NULL
select ss.int_tran_id 
from status ss, app_data ad, ach aa
where ss.int_tran_id = ad.int_tran_id
and ad.app_data_id = aa.ach_id
and ss.stage = 'PREVDAY'
and aa.par_number IS NOT NULL


my variant:

select ss.int_tran_id 
from status ss
left join references rf on (ss.stage = 'PREVDAY'
                            and ss.int_tran_id = rf.int_tran_id
                            and rf.mid_ref IS NOT NULL)
left join app_data ad on (ss.stage = 'PREVDAY'
                          and ss.int_tran_id = ad.int_tran_id)
left join ach aa on (ss.stage = 'PREVDAY'
                     and ad.app_data_id = aa.ach_id
                     and aa.par_number IS NOT NULL)
where ss.stage in ('ACHPayment_Confirmed', 'HIFV4', 'HIFV5_FTRINF', 'Payment_HIFV5_FTRINF')
   or rf.int_tran_id is not null
   or aa.ach_id is not null
By : SkyWalker

Consider writing the entire query in one SELECT (using bracketing, ANDs, ORs), this removes all the inefficient duplicate checking of UNION.

You can try UNION ALL as Pawel-Dyl suggests, but if you can make that work, you can probably write it all in one SELECT, and this gives the optimizer more options.

By : RobertB

I think you could rewrite this as a single query on the status table, using ORs to test each scenario, like so:

SELECT ss.int_tran_id
FROM   status SS
WHERE  ss.stage in ('ACHPayment_Confirmed', 'HIFV4', 'HIFV5_FTRINF', 'Payment_HIFV5_FTRINF')
OR     (ss.stage = 'PREVDAY'
                     FROM   references rf
                     WHERE  ss.int_tran_id = rf.int_tran_id
                     AND    rf.mid_ref IS NOT NULL)
                        FROM   app_data ad
                               INNER JOIN ach aa
                                 ON (ad.app_data_id = aa.ach_id)
                        WHERE  ss.int_tran_id = ad.int_tran_id
                        AND    aa.par_number IS NOT NULL)));
By : Boneist

This video can help you solving your question :)
By: admin