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