Re: SQL and UNION: why do some UNIONs significantly inflate the processing time, while others don't?

@union all: use whatever your appplication needs! union all might be (slightly) faster reading from the local database, eliminating duplicates by the database might be faster reading from a remote database!!!
@why could it be slower than adding the times: simply because the database is using diffrent access methods! This could be caused by a timeout of the database optimisation process. Adding up the optimisation effort for 10 selects might break the timeout for the optimisation process, so the database is using the best choice at this time - and this could be a "second best choice". Another variant could be, that the optimisation changes from best runtime to best ressource usage, becuase the estimated runtime breaks some ressource limits.

This could be evaluated by database monitor (this would measure real productive runtimes). Mostly a first look to the debug messages of the optimizer (simply set the job to debug) would be sufficient for an experienced person (might be easier with a graphical tool for younger people).

Recommendations:
- for offline query usage adding substracts to a workfile by bulk sql statements will bring, what you are expecting.
- for transactional usage: in many cases the need of union is caused by a suboptimal database design and solving this, would solve the problem!!!
(just a little simplified example:
having tables prices2010, prices2011, prices2012. ... prices2021 and view prices as (select ... from prices2010 union prices2011 union ... prices2021

better would a table prices with an additional column year and views prices 2010 as select ... from prices where year = 2010, prices2011 .. )

D*B