I have two tables; the first table has 2 million records, where 1 column is called DEA_NO, which 1 million records has DEA_NO = NULL. The table has a non-clustered index on this column. The second table has about 140 records, and a column called DEA_NO (same type and size as in table one). Using the following query:
Set A.ME_Number = Table_One.ME_Number
From Table_Two A, Table_One(3)
Where A.DEA_NO = Table_One.DEA_NO
with ShowPlan ON, Sybase shows that it wants to use Table_One first (with the forced index) matching Table_Two with a table scan. Unfortunately, this actually works like a table scan on Table_One anyway, only we are traversing the table in DEA_NO order. Why does Sybase insist on using Table_One first, instead of moving through the 140 records in Table_Two, looking up the records (which are indexed) in Table_One.