> Are the statistcs up to date ? > Statistics are not something I understand well. I have run "SET STATISTICS INDEX USV_SUPPROG_OWNER_USER_ID" on all indexes of the two tables. I'm guessing that brings them up to date, correct? No change in the query.
The two tables you mentioned has the same amount of records ? The > statistics are quite diferent, so, or the number of rows are bery diferent > or the index statistics are out of date. > SchlHist has 287565 records and 229320 where Advocate_Code is not null. SupProg has 929329 records and 2792 where Advocate_Code is not null. > Try this one: > > select sp.STUDENTSEQ, a.User_ID > from supprog sp > join Advocate a on sp.ADVOCATE_CODE=a.Advocate_Code+0 > where a.USER_ID=37 > Because Advocate_Code is not an integer, I changed it to a.Advocate_Code || ''. And this does indeed cause it to use the index and run fast. I'd hate to have to use that as a final solution. Thanks for your input.
