Union SQL query performance

Question

I have a table which gives a value between two items. The table has the following columns :

itemA | itemB | value
1     | 2     | 125
1     | 3     | 56
3     | 2     | 78
2     | 4     | 24
4     | 1     | 994
3     | 4     | 7

I highlight that the symetric values are not repeated. For example there is now row with itemA = 2 and itemB = 1 with value = 125 as this data is already stored with itemA = 1 and itemB = 2.

Now I would like to get all the values between items 1, 2 and 3 but with the symetric values as well. Something like that in the SQL result set :

itemA | itemB | value
1     | 2     | 125
1     | 3     | 56
2     | 1     | 125
2     | 3     | 78
3     | 1     | 56
3     | 2     | 78

I succeed to get that with the follwoing SQL query :

select itemA as iA, itemB as iB, value 
from table 
where itemA in (1,2,3) 
    and itemB in (1,2,3) 
union all 
select itemB as iA, itemA as iB, value 
from table 
where itemA in (1,2,3) 
    and itemB in (1,2,3) 
order by iA, iB;

However, it has been said to me that UNION operator is performance killer and this table has in fact more than 100 M rows. But after hours of brain storming, I don't find another way to get that. Please, could you see a better, more performant SQL query to get this result set.

Thank you in advance

looks more for codereview than stackoverflow. By the way, mentioning the DBMS really never hurts... — gdoron
– gdoron, Commented Sep 23, 2013 at 15:04
Yep, database is : Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production — Comencau
– Comencau, Commented Sep 23, 2013 at 15:15

Daniel Hilgarth · Accepted Answer · 2013-09-23 15:07:31Z

2

UNION can be a performance killer because it is keeping only distinct items.
UNION ALL on the other hand simply concatenates the result sets, so there should be no significant performance hit.

But you really should measure it or check the explain plain to be sure.

answered Sep 23, 2013 at 15:07

Daniel Hilgarth

175k41 gold badges346 silver badges450 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Union SQL query performance

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related