>>>>How exactly are the fields indexed? For example, is the index key UPPER(f1) or f1+f2+f3+f4? If it's just plain f1 I would expect that join to be optimized. You should try it in the WHERE clause to see if it makes a difference. It's also possible that SYS(3054) only looks for optimization in the WHERE clause and won't find it in the JOIN clause even if it is optimized. I would do some expirimentation and see what works. Rushmore is a strange and wonderful thing!
>>>>
>>>>>I am trying to join a 300K table to one that has
>>>>>about 5 million records. The 1 to many join is
>>>>>based on 4 fields. All 4 fields in both tables
>>>>>are indexed.
>>>>>
>>>>>Is it best to use the ANSI 92 JOIN ON with all
>>>>>4 fields combined with an AND or should I use the
>>>>>WHERE clause? I tried using the SYS(3054,11) command
>>>>>and am getting 'none' as the optimization level.
>>>>>
>>>>>What can I do to optimize this join?
>>>>>
>>>>>The info below represent what I have now:
>>>>>
>>>>>SELECT tablea.* ;
>>>>> from tablea aa ;
>>>>> JOIN tableb bb ;
>>>>> ON aa.f1 = bb.f1 AND ;
>>>>> aa.f2 = bb.f2 AND ;
>>>>> aa.f3 = bb.f3 AND ;
>>>>> aa.f4 = bb.f4 ;
>>>>> INTO CURSOR cx
>>>
>>>I have individual index tags on each field (4 tags on each table). The tags
>>>are simply on the field - no upper() or other functions are used.
>>
>>How you will use the result of this join. It's just a curiosity, because 300K joint to 5000K looks scary.
>
>I expect the result set will be 1000K records.

Yes, but where the result going? Report, grid? There are some situations when query time might be reduced by purely application design/flow solutions. In regard to query: what is datatype for these fields and do you have DELETED() tags in both tables?