Application Design and Tuning for Performance › Search Strategies › Name Searching › Common Table Expression

Common Table Expression

The following example shows a common table expression (DB2 V8, DB2 9) to build a search list, and then divides that table into the person table. This query offers good index matching and reduced probes.

WITH PRSN_SEARCH(LASTNAME, FIRST_NAME) AS
(SELECT 'RADY', 'BOB' FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT 'BOB', 'RADY' FROM SYSIBM.SYSDUMMY1)
SELECT PERSON_ID
FROM   PERSON_TBL A, PRSN_SEARCH B
WHERE  A.LASTNAME = B.LASTNAME
AND    A.FIRST_NAME = B.FIRST_NAME