FIX: MERGE UNION Is Not Used with Large Number of UNION Clauses (295037)

SYMPTOMS

Queries that use more than 20 UNION ALL clause branches are executed with a HASH union, instead of the more efficient MERGE UNION, which causes excessive memory usage and may result in the failure of the query. An implication of this is that a distributed partitioned view running on 24 nodes has different, and less efficient, plans than a view on 20 nodes.

RESOLUTION

To resolve this problem, obtain the latest service pack for SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

MORE INFORMATION

Steps to Reproduce Behavior

Open a window in Query Analyzer.
On the Query menu, select Show Execution Plan.

Execute the following query:

use Northwind
go
select top 1 * from
(
select orderid from orders union all
select orderid from orders union all
select orderid from orders union all
select orderid from orders union all
select orderid from orders union all
select orderid from orders union all
select orderid from orders union all
select orderid from orders union all
select orderid from orders union all
select orderid from orders union all
select orderid from orders union all
select orderid from orders union all
select orderid from orders union all
select orderid from orders union all
select orderid from orders union all
select orderid from orders union all
select orderid from orders union all
select orderid from orders union all
select orderid from orders union all
select orderid from orders 
union all select orderid from orders
) xx
order by orderid

Click the Execution Plan tab and observe the results.

Comment out the following line of the query:

union all select orderid from orders

Execute the edited query and observe the change in plan on the Execution Plan tab.