Some general questions about UNION ALL

[tbtcust]

Hi all. Questios:

Is there a limitation on the number of UNION ALL in a single query?

Is there a performance impact using UNION ALL?

Is there a better or preferred way of selecting the data?

Thanks in advance for any help.

 

[gregsimpson]

UNION ALL often has less performance implications than UNION, as UNION uses sort to remove duplicates, where UNION ALL doesn't.

Cheers
Greg

 

[tbtcust]

Thanks Greg. Do you know if there is a limitation on the number of UNION ALL in a query? I found a post that said 16 was the Limit. However I have not been able to confirm that for the latest version of DB2. Thanks again.

 

[MarcLodge]

tbt103,
I just ran

SELECT IBMREQD
FROM "SYSIBM".SYSDUMMY1
UNION ALL
repeated n times

in my v8 QMF. Started off with 16 UNION ALLs and kept going until it didn't like it any more. It got up to 512 ok, but on 513 it gave me the error:
"Your query is too long or too complex."

It may be that 512 is NOT the limit of UNION ALL, but just that the number of characters in the SQL statement is too many. I know that in the past I have had a problem with very large SQL statements giving this sort of error.

Sorry that I can't be any clearer.

Marc

 

[tbtcust]

Thanks MarcLodge. That helps a lot. I think if there is a limitation, it would be with the length of the query.