Skip to Content
Information

This site will undergo a brief period of maintenance on Friday, 18 December at 12:30 AM Central/12:00 PM IST. During a 30 minute window, site availability may be intermittent.

Observation: Correlated Subquery uses EXISTS or NOT EXISTS

You coded a subquery using EXISTS or NOT EXISTS predicates. If the subquery is a correlated subquery, the Db2 optimizer will not consider access through an available index.

The following example includes a correlated subquery using EXISTS:

Information
Example

SELECT * A.empno, A.lastname, A.firstname 
FROM T1 A 
WHERE EXISTS 
(SELECT * FROM T2 B 
WHERE B.empno = A.empno); 

Consider recoding the correlated subquery as a JOIN and referencing any indexed columns:

Information
Example

SELECT A.empno, A.lastname, A.firstname FROM T1 A, T2 B 
WHERE A.empno = B.empno;

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*

BMC AMI iStrobe Web 24.01