SQL Tuning [Electronic resources] نسخه متنی

7.4 Queries with Set Operations

Occasionally,



you must tune
multipart queries that use set operations like
UNION, UNION ALL,
INTERSECT, and EXCEPT to
combine results of two or more simple queries. The extension of the
SQL-diagramming tuning method to these multipart queries is usually
straightforward: diagram and tune each part independently, as if it
were a standalone query. When the parts are fast, combining the
results with set operations generally works well.

EXCEPT is the keyword specified by the ANSI SQL standard for the set operation to find the difference between two sets. DB2 and SQL Server follow the standard by supporting EXCEPT. Oracle, however, uses MINUS for the same operation, most likely because it supported the operation before the standard existed.

However, some of these set operations deserve a little extra
discussion. The UNION operation, in addition to
combining the parts, also must sort them and discard duplicates. This
last step is often unnecessary, especially if you design the parts to
avoid duplicates in the first place. In Oracle, you can replace the
UNION operation with UNION ALL
when you determine that duplicates are either impossible or need not
be discarded. In databases that do not support UNION
ALL, you can skip the duplicate-eliminating step by
replacing the single UNION query with two or more
simple queries, combining the results in the application layer,
rather than in the database.

The INTERSECT operation can generally be
profitably replaced with an

EXISTS-type
subquery that looks for the matching row that the second part would
produce. For example, if you had two Employees
tables, you might look for shared employee records with this:

SELECT Employee_ID FROM Employees1
INTERSECT
SELECT Employee_ID FROM Employees2

You could always replace this INTERSECT query with
this:

SELECT DISTINCT Employee_ID 
FROM Employees1 E1
WHERE EXISTS (SELECT null 
FROM Employees2 E2
WHERE E1.Employee_ID=E2.Employee_ID)

Using the methods of Section 7.3, you would then determine
whether this EXISTS subquery should be expressed
in the EXISTS or IN form, or
converted to a simple join. Note that the correlating join conditions
become numerous if the SELECT list contains many
items. Also note that INTERSECT will match column
lists with nulls, but a correlation join will not, unless you use
join conditions designed for that purpose. For example, if the
positive-valued foreign key Manager_ID is allowed
to be null (but Employee_ID is not), the Oracle
equivalent of this query:

SELECT Employee_ID, Manager_ID FROM Employees1
INTERSECT
SELECT Employee_ID, Manager_ID FROM Employees2

is this query:

SELECT DISTINCT Employee_ID, Manager_ID 
FROM Employees1 E1
WHERE EXISTS (SELECT null 
FROM Employees2 E2
WHERE E1.Employee_ID=E2.Employee_ID
AND NVL(E1.Manager_ID,-1)=NVL(E2.Manager_ID,-1))

The expression NVL(...,-1) in the second
correlation join condition converts null values on the nullable
column so that they join successfully when null is matched with null.

The EXCEPT (or MINUS) operation
can generally be profitably replaced with a

NOT
EXISTS-type subquery. Searching for employee
records in the first table but not in the second table, you might
have used this:

SELECT Employee_ID FROM Employees1
MINUS
SELECT Employee_ID FROM Employees2

You could always replace that with this:

SELECT DISTINCT Employee_ID 
FROM Employees1 E1
WHERE NOT EXISTS (SELECT null 
FROM Employees2 E2
WHERE E1.Employee_ID=E2.Employee_ID)

You would then solve this query using the methods described in Section 7.2.