Union 隐藏的优势

最新推荐文章于 2025-10-29 13:13:15 发布

转载最新推荐文章于 2025-10-29 13:13:15 发布 · 840 阅读

SqlServer 专栏收录该内容

25 篇文章

订阅专栏

本文探讨了在 SQL Server 中使用 UNION 的高效方法，通过创建复合索引来优化查询性能，以及如何避免 OR 运算符导致的慢速执行。

Hidden Power of UNION

Readers

SQL Server Pro

InstantDoc ID #37531

I used to think that UNION was useful only when you needed to combine the results of two queries from different sources (e.g., tables, views) into one result set. However, using UNION is sometimes the quickest way to select from just one table.

Suppose you need to retrieve all OrderIDs from the Northwind Orders table where the CustomerID is VICTE or the EmployeeID is 5:

SELECT OrderID FROM Orders
WHERE CustomerID = 'VICTE' OR EmployeeID = 5

The Orders table has indexes on the CustomerID and EmployeeID columns, but SQL Server doesn't use them to execute the SELECT statement, as the following execution plan shows:

|--Clustered Index Scan(OBJECT:
([Northwind].[dbo].[Orders].[PK_Orders]
), WHERE:([Orders].[CustomerID]='VICTE'
OR [Orders].[EmployeeID]=5))

Instead, SQL Server scans the clustered index PK_Orders. SQL Server uses this plan because the OR operator in the query's WHERE clause makes the result satisfy both conditions at the same time, so SQL Server must double-scan the table. Scanning a clustered index in this case is almost the same as scanning the entire table; the server goes though the table record by record and checks for the specified predicate.

To improve the query's efficiency, you could create a composite index on CustomerID and EmployeeID:

CREATE INDEX IdxOrders001 ON Orders
(CustomerID, EmployeeID)

The Index Tuning Wizard advises you to create just such an index to improve performance of the SELECT statement. SQL Server can use the new index to find all the records in which EmployeeID = 5, then scan only the resulting range of records to return the required result. The estimated execution plan for the new query shows that SQL Server uses the composite index:

|--Index Scan(OBJECT:([Northwind]
   .[dbo].[Orders].[IdxOrders001]),
   WHERE:([Orders].[CustomerID]='VICTE' OR
   [Orders].[EmployeeID]=5))

But an employee can make thousands of deals with thousands of customers. So even if you create a composite index, SQL Server will need to scan a range of records in the index and won't perform a seek operation, which uses indexes to retrieve records and is the fastest way for SQL Server to find information.

You need to make SQL Server use a seek operation instead of a scan, but SQL Server won't perform a seek when an OR operator is in the WHERE clause. You can solve this dilemma by using UNION to rewrite the SELECT statement:

SELECT OrderID FROM Orders
WHERE CustomerID = 'VICTE'
UNION
SELECT OrderID FROM Orders
WHERE EmployeeID = 5

SQL Server 2000's estimated execution plan for this statement is

|--Merge Join(Union)
    |--Index Seek(OBJECT:([Northwind]
       .[dbo].[Orders].[CustomersOrders]),
       SEEK:([Orders].[CustomerID]='VICTE'
       ) ORDERED FORWARD)
    |--Index Seek(OBJECT:([Northwind]
       .[dbo].[Orders].[EmployeesOrders]),
       SEEK:([Orders].[EmployeeID]=5)
       ORDERED FORWARD)

This execution plan looks longer than the original one, but both operators are index-seek operators. SQL Server doesn't use the composite index; instead, it uses two single-column indexes. You might think that two seek operations would cost more than one seek, but performance improves when you use this method. You can check performance by using SQL Trace to analyze the three versions of the SELECT statement. For the UNION query, my SQL Server 2000 system performed four reads to return the result. The first SELECT query required 23 reads, and the second SELECT statement, which created the composite index, required 11 reads.

This special use of UNION can help you avoid the OR operator's slow performance, but use it carefully. If you don't have the appropriate indexes (CustomersOrders and EmployeesOrders, in this example), you can double-scan the table.

—Oleg Dantchenko
oleg.dantchenko@acs-inc.com