>Hi!
>
>It seems you was not very accurate when reading my message. I'm not propose to use equivalent. I just tried to explain why your query is not optimized. Read it again you will find answer there.

Vlad,

I did read your message and understand it.

Yes, in your example, the arappx did not have indexes, and so of course doing a LOCATE against it without an index would be slow as the loop runs. You mention after your example that if I reverse the loop (loop through arappx and do LOCATEs against hpaph0) then it will be optimized. This is true...the LOCATES will find an index tag on hpaph0 and use it.

That is the exact problem. Though a loop would be optimized, the SQL statement is telling me that there is NO optimization occuring on HPAPH0. That is the part I don't understand. Let me reiterate with a follow-up example:

Here is the query:

SELECT hpaph0.* FROM hpaph0, arappx ;
WHERE hpaph0.aphst_ = arappx.appst ;
AND hpaph0.aphcty = arappx.appcty ;
AND hpaph0.aphpol = arappx.apppol ;
AND hpaph0.aphyr = arappx.appcyr ;
INTO CURSOR cResult

Now, in my more recent tries, I have gone one step further. I now have index tags on the following fields:

In hpaph0: aphst_, aphcty, aphpol, apyyr
In arappx: appst, appcty, apppol, appcyr

In other words, every field being used for the join is indexed. Here is what I get as the results from SYS(3054,11) when I run the above query:

Rushmore optimization level for table hpaph0: none
Rushmore optimization level for intermediate result: none
Joining intermediate result and table hpaph0 using temp index

I am not sure what all of that means, but I know that despite everything being indexed, there is no optimization going on, Fox is building a temp index somewhere, and the query runs slow as a dog. HPAPH0 is only going to get bigger, and it would be nice if this query went fast.

Thanks for taking the time to read and respond. I hope this message is more clear on what is happening...

Thanks,
JoeK