performance of an union query

[Jan359]

I am working on a query with a union and the performance of the query is very bad (about 3500 seconds). When i run both queries apart, they take both about 100 seconds. How is this possible?

 

[Roeland]

Is it a "union" or a "union all"?
That's a great difference.

If it's a "union" then oracle have to eliminate all the duplicates...

Roeland

 

[Jan359]

hey Roeland, thanks for your answer. it's an union. but there are no duplicates

 

[Marco Kalter]

Performance-wise it doesn't matter whether there are duplicates or not. If you are using a "union" instead of a "union all", Oracle has to test for duplicates for each row.

Try a "union all" and see what happens.

 

[Jan359]

Marco, this is the answer i neeeded! It works. Thanks a lot!!

Jan