>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 would do two things:

1. INDEX both tables ON DELETED() TAG DELETED.

2. INDEX both tables ON F1+F2+F3+F4 TAG F1F2F3F4 (I'm assuming F1, F2, and so on, are strings; if they were numeric, I would use something like STR(F1)+STR(F2)+STR(F3)+STR(F4).)

The join would be ON AA.F1+AA.F2+AA.F3+AA.F4=BB.F1+BB.F2+BB.F3+BB.F4.

I have done similar things before, and they get optimized.

HTH!