Which table first?

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:

Update Table_Two
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.  

Thanks.

Brent
LVL 6
bknouseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jbiswasCommented:
Looks like you are working with Doctor profiles for a pharmaceutical application. Firstly it would help to know what sybase software you are using. If you are SQL Anywhere, you basically can't do much, except maybe try and reindex both the ME# and DEA# fields in the two tables.

If you are using Sybase then what version of sybase are you using. Syatem 10 was notorious for doing what you are observing , but I haven't seen anything like this with system 11. Either way, you can drop and recreate the index (on both ME# and DEA#) and see if anything is different. If that doesn't work, try using the "forceindex" clause. I haven't used it in a while, so please look up forceindex in the sybooks documentation. It tells you how to force the query optimizer to use your index.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.