Application Design and Tuning for Performance › Existence Checking › ORDER BY and FETCH

ORDER BY and FETCH

As of DB2 9, it is possible to code and ORDER BY and FETCH first in a subquery, which can provide even more options for existence checking in subqueries.

SELECT DISTINCT SNAME
FROM   S, SP
WHERE  S.S# = SP.S#
AND    SP.P# = 'P2'

For singleton existence checks, you can code FETCH FIRST and ORDER BY clauses in a singleton SELECT. This practice could provide the best existence checking performance in a stand-alone query:

SELECT 1 INTO :hv-check
FROM   TABLE
WHERE  COL1 = :hv1
FETCH  FIRST 1 ROW ONLY