>>>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.