Skip to Content

Observation: Subquery Uses IN

You coded a correlated subquery or a subquery using the IN predicate. The DB2 optimizer in Db2 Version 5 will not consider available indexes in either type of subquery. Starting in Db2 Version 6, the optimizer will consider available indexes if the subquery is not correlated.

The following is an example of a subquery using IN:

Information
Example

SELECT empno, lastname 
FROM T1 
WHERE workdept IN 
(SELECT deptno 
FROM T2 
WHERE deptname = 'X'); 

Consider recoding the subquery as a JOIN to allow the Db2 optimizer to consider an available index.

Information
Example

SELECT empno, lastname 
FROM T1, T2 
WHERE workdept = deptno 
AND deptname = 'X';

 

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

BMC AMI iStrobe Web 23.01