Dragan,

Thanks for the response :) Your comments are really welcome (I have a habit to talk to myself, I admit :))

TranMstr tabe now has a Lender tag.
Lender table has a code tag (Lender is a Lookup table, has all lenders and their details).
Lender table is not used in where expression.
The exact SQL looks like:

select ccode, town, date, mortgage, parentcode, ;
source, extrweek, extryear ;
from TranMstr inner join SiteMstr ;
on TranMstr.PropID=SiteMstr.PropID ;
inner join Lookups!Lender on TranMstr.Lender=Lender.code ;
where Source='S' and between(mortgage, 25000,500000) ;
into table (qry_arg)

SQL uses temp index, which I don't understand.

BTW, I noticed the similar behavior in different situation. The similarity here is that in both cases field and tag name don't match in joining tables.

Do you have some explanations?

Thanks a million in advance.

>>>>I run the following SQL:
>>>>select * from TranMstr inner join Lender on TranMstr.Lender=Lender.code where etc.
>
>>>Ok, disregard my question. I was sure, TranMstr has Lender index, but I just checked, it it doesn't! So we will add this index to TranMstr table.
>>
>>Ok, I added Lender tag in TranMstr, but it still is not used in optimization. It still shows 'using temp index'...
>>Does it mean, that both tags should have the same name 'Lender' in order to be used in Join optimization?
>
>Do you have a tag on lender.code, and does the where clause involve some other things which may lead to a need for a temp index?
>
>p.s. sorry to interrupt your conversation :)

 If it's not broken, fix it until it is.


My Blog