Sergey,
>The SYS(3054,11) output shows that the JOINs in the query are optimized but WHERE clause is not. Try
>

>SELECT * ;
>	FROM person p ;
>		JOIN ( SELECT cl_ref, GOMONTH(MIN(EVL(re_date, {^9000-12-31})), -216) AS AdjRefDate ;
>				FROM referral GROUP BY cl_ref ) r ;
>			ON p.cl_ref = r.cl_ref ;
>	WHERE p.cl_dob > r.AdjRefDate
>

>>>How many records are in each table?
>>
>>Person = 19650
>>Referral = 52186
>>
as the where clause doesn't compare to a constant value but a field in the table to be joined I don't believe it can be optimized by rushmore: the optimizer would have to "understand" the "distinctness" of cl_ref in the subselect. ***If*** that was not the case, how should a record in p be flagged which has cl_dob> AdjRefDate of Recno()=1 but cl_dob< AdjRefDate of Recno()=15 ? But since cl_ref is unique, perhaps a compound index could be used to bisect p for each record in the sub-select. Maybe Aleksey will chime in, but I think this could be one of the situations where hand-optimization can make a difference. One of the things I'ld try would be a double scan loop with r in the outer loop jumping over all the records in person with the same cl_Ref but not fitting the date criteria. The effectiveness should grow the smaller reccount() is in the grouped sub-select, giving each r.cl_Ref the possibility of dicarding many records on p.cl_dob without actually checking them, as I guess the SQL has to do.

regards

thomas