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