I don't think, this is the point (of course, I can add this index to this table, but I doubt, I gain from it). If I have something like
select * from SiteMstr inner join PropMstr on SiteMstr.PropID=PropMstr.PropID...
I belive, I had full optimization for both tables. In this case I have different name for Key field. Could this cause this problem? Also, ZipCode is not a foreign key for SiteMstr (as PropID), but just a regular field.

I'll run couple of tests today to test...

Ok, here is the test, I've ran now:

sys(3054,11)
select tn1.* from towns tn1 inner join towns tn2 on tn1.town=tn2.town

I see full optimization for both tables.

With set deleted off I see none optimization for both. So, you're right, deleted() index will make a difference in SQL show plan. However, my question is about speed. Will it help to speed up the query?

>I also get NONE on second table until I also added an INDEX ON DELETED().
>
>>>Is c:\wistemp\btc_0IU0ZA4P5 a temp cursor or a real DBF? When you created the index did you create a structural index [CDX] or an IDX?
>>
>>It's a table and has CDX index
>>
>>>>Hi everybody,
>>>>
>>>>I'm running this simple test:
>>>>
>>>>

>>>>SELECT sitemstr.* ;
>>>>       from o:\redp\dbc\ma\sitemstr ;
>>>>       inner join c:\wistemp\btc_0IU0ZA4P5 btcZipcode
>>>>          on sitemstr.zipcode = btcZipCode.btcCode ;
>>>>              INTO TABLE ..\..\work\query\qryU0ZA34J

>>>>
>>>>Temp table has an index on btcCode and SiteMstr has an index on ZipCode. sys(3055,11) shows, that optimization level for SiteMstr is full and for temp table is none. The query executes very long time (SiteMstr has ~3mln. records). The original query (not the test one) has 7 joins already and it takes forever. Is it here a way to optimize it?
>>>>
>>>>I've tried to change select ... where ZipCode in (select btcCode from ) form and it also shows none optimization for temp table, though seems to execute a little bit faster (still very slow).