>>>>Hi everybody,
>>>>
>>>>I'll try to rephrase my previous post, since I got 0 answers. Suppose, I have two tables, which I want to join:
>>>>Table1 with field code1 and index on code1 tag code1
>>>>Table2 with field code2 and index on code2 tag code2
>>>>
>>>>select * from Table1 inner join table2 on code1=code2 ...
>>>>It seems to me, that if tag names are different (code1 and code2), these indexes aren't used in optimization. Instead SQL creates temp index.
>>>>
>>>>Could somebody confirm this behavior? Why it's so?
>>>>
>>>
>>>Rushmore uses index expressions, not names, to decide what index to use.
>>
>>Ok, why it doesn't use code1 index and code2 index in this case? What's going on here?
>>
>
>How are you determining that the indexes are not being used?
Erik,

This is the SQL:

SELECT ccode, town,  Date, Mortgage, Source, ParentCode as Lender, extrWeek, extrYear FROM TranMstr inner join Lookups!Lender on TranMstr.Lender=Lender.Code Inner Join SiteMstr on TranMstr.PropID=SiteMstr.PropID where between(mortgage,25000,500000)  AND extrYear+extrWeek<=oJC.HighWeek and year(date)=val(oJC.CalYear)  INTO TABLE (qry_arg3)

And this is Rushmore optimization:

Using index tag Mortgage to rushmore optimize table tranmstr
Using index tag Issueweek  to rushmore optimize table tranmstr
Using index tag Deleted to rushmore optimize table tranmstr
Rushmore optimization level for table tranmstr: partial
Rushmore optimization level for table lender: none
Using index tag Deleted to rushmore optimize table sitemstr
Rushmore optimization level for table sitemstr: full
Joining table sitemstr and table tranmstr using index tag Propid
Joining table lender and intermediate result using temp index

As you can see, code and lender indexes are not used in optimization. This SQL runs extremely long...
Do you see problems with it? Why it doesn't use existing indexes?

Thanks a lot in advance.