Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Which table first?

Posted on 1998-03-18
1
396 Views
Last Modified: 2010-05-18
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
0
Comment
Question by:bknouse
1 Comment
 
LVL 2

Accepted Solution

by:
jbiswas earned 50 total points
ID: 1098378
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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article describes how to reset your Windows 10 password when you've forgotten it.
The advancement in technology has been a great source of betterment and empowerment for the human race, Nevertheless, this is not to say that technology doesn’t have any problems. We are bombarded with constant distractions, whether as an overload o…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question