Help

Observation: Quantified Predicate with Subselect

You coded a subselect using a quantified predicate. If possible, consider recoding the predicate using either an explicit value or a subselect that returns an atomic value.

The example below uses a quantified predicate to determine which employees have worked more overtime than is allowed for any project:

SELECT 
FIRSTNAME, 
LASTNAME, 
FROM 
EMPLOYEE 
WHERE 
OVERTIME > ANY 
(SELECT 
OVERTIME 
FROM 
PROJECT) 

If you knew that employees cannot work more than 100 hours of total overtime on any project, you can recode the statement using that explicit value, as shown below:

SELECT 
FIRSTNAME, 
LASTNAME, 
FROM 
EMPLOYEE 
WHERE 
OVERTIME > 100 

Or, in the case of predicates coded using ANY and SOME, you can reduce the subselect to an atomic value using the MIN column function, as shown below:

SELECT 
FIRSTNAME, 
LASTNAME, 
FROM 
EMPLOYEE 
WHERE 
OVERTIME > 
(SELECT 
MIN(OVERTIME) 
FROM 
PROJECT) 

Note: If there is an ascending index on OVERTIME, the subselect results in a 1-fetch index scan.

You can apply the same transformation for quantified predicates coded with ALL by using the MAX column function in the subselect. For this transformation, the subselect results in a 1-fetch index scan if there is a descending index on OVERTIME.