DB2 UDB for z/OS: Design Guidelines for High Performance and Availability

Chapter 8. SQL fundamentals 195

Note that the ORDER BY columns do not have to be in the list of selected columns.

8.5 Subquery to join transformation

Depending on several conditions, DB2 can perform a subquery to join transformation for

SELECT, UPDATE, and DELETE statements between the result table of a subquery and the

result table of an outer query.

A complete list of conditions for a subquery to join transformation can be found in DB2 for

z/OS Application Programming and SQL Guide, SC18-7415-02.

A major advantage of subquery to join transformation is the flexibility for DB2 to decide the

most efficient order to process the tables.

It is possible that, in some cases, subquery to join transformation will degrade performance.

This is most likely to happen when the outer query returns a small number of rows and the

subquery returns a large number of rows. But you can also find the same effect the other way

around. In such a case, transformation provides no performance benefit, but there is

additional overhead to prepare the statement with transformation activated.

You can find a candidate for a noncorrelated subquery to join transformation in Example 8-7.

Example 8-7 Candidate query for subquery to join transformation

SELECT * FROM T1

WHERE COLa IN

(SELECT COLa FROM T2

WHERE COLb = ’10’);

Since you do not tell DB2 that the select statement is read-only, DB2 is looking for an access

path where it can maintain the current cursor position to allow for updates on the result set.

If you specify FOR FETCH ONLY, then DB2 does not need to know the current position of

your cursor on the base table, because you are not going to update qualifying rows.

Therefore, a more efficient access path for read-only cursors may be available to the

optimizer. See Example 8-8 to allow the optimizer for another join sequence.

Example 8-8 Specifying FOR FETCH ONLY to improve optimizer’s choices

SELECT * FROM T1

WHERE COLa IN

(SELECT COLa FROM T2

WHERE COLb = ’10’)

FOR FETCH ONLY;

Tip: PTF UQ50928 for APAR PQ45052 provides you with the ability of disabling correlated

subquery to join transformation in DB2 Version 7 at the system-wide level.

Tip: While FOR UPDATE OF forces a certain access path, FOR FETCH ONLY provides

more opportunities to the optimizer to choose the most efficient access path.