>>>>>>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?
>
>Results are going to a table. Data type for all join fields are character
>and there are NO deleted records. Actually you will see other problems when
>using a deleted() tag such as when browsing a very large table for the first
>time. I know that without it the SYS(3054) will not show full optimization but
>that is the price you pay...

You know, I didn't see problems with Deleted() tag on huge table. I still don't understand what is the purpose of this process (probably, because it's Friday :). Is it part of data conversion, or import. It's difficult to imagine routine application process which creates 1 million record table. I mean, that if it's not regular operation, then optimization is not very important.