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

Refresher

A subquery is a SELECT statement with another SELECT inside it. It looks like this:

SELECT ...         /* outer or parent query */
(SELECT ...)     /* subquery, inner query or subselect */
...

There are three possible plans to process a subquery:

flattened.
Transform the query to a join, then process as a join.

out-to-in.
For each row in the outer query, look up in the inner query.

in-to-out.
For each row in the inner query, look up in the outer query.

When processing is out-to-in, the outer query is the driver. When processing is in-to-out, the inner query is the driver.

Table 6-1 shows the SQL Standard requirements and the level of support the Big Eight have for subqueries.

Notes on Table 6-1:

Basic Support column

This column is "Yes" if the DBMS fully supports the [NOT] IN, [NOT] EXISTS, <comparison operator> ANY, and <comparison operator> ALL predicates, and correctly handles subquery situations where the number of rows is zero.

Row Subquery column

This column is "Yes" if the DBMS supports row subqueries, for example:

SELECT * FROM Table1
WHERE (column1, column2) =
(SELECT column1, column2 FROM Table2)

Table Subquery column

This column is "Yes" if the DBMS supports table subqueries, for example:

SELECT *
FROM (SELECT * FROM Table1) AS TableX

Max Depth column

Shows the maximum number of subquery levels supported.

Allow UNION column

This column is "Yes" if the DBMS allows UNION in a subquery.

Types Converted column

This column is "Yes" if the DBMS automatically casts similar data types during comparisons.