how to improve union performance?

[777555]

Hello,

I have a query which needs to return thousands rows from
a multiple union sql statements, like the following:

select col1, col2, col3 from table1 where ...
union
select col1, col2, col3 from table2 where ...
union
select col1, col2, col3 from table3 where ...

The three tables have the similar schema and each of them
has about million records. All of three tables have indexes. If running each statement individually, I can get the result quickly. With union all of them together, the query is very slow.

Can somebody help on this?

 

[blom0344]

Do you need 'union' instead of 'union all'? The use of 'union' will enforce the removing of duplicates, which may slow down things. 'Union all' will just return the sets 'as is'. In some cases this is quite good enough....

Ties Blom
Information analyst