Hi Sergey

The WHERE is still not optimized...

Kev

>Hi Kavin,
>
>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
>

>
>>>Can you post SYS(3054,11) output?
>>
>>Rushmore optimization level for table p: none
>>Rushmore optimization level for table rr: none
>>Rushmore optimization level for intermediate result: none
>>Joining intermediate result and table rr using index tag Referra5
>>Rushmore optimization level for table p: none
>>Rushmore optimization level for table r: none
>>Rushmore optimization level for intermediate result: none
>>Joining intermediate result and table p using index tag Client1
>>Joining intermediate result and table r using index tag Referra5
>>
>>>How many records are in each table?
>>
>>Person = 19650
>>Referral = 52186
>>
>>>Is query optimizable if you run it w/o WHERE caluse ?
>>
>>No.
>>
>>>Can you create an index on cl_dob field and run query again?
>>
>>There is already an index on cl_dob.
>>
>>Kev