December 25, 2008 at 10:22 pm
hi i hav an update query which show table scan m confused how to i put indexes so that it does not scan the table
Update temp1 Set Date = NULL
Where (Amount <> Updated or Amount1 <> Updated1 or
Amount3 <> Paid) and Date is Not Null And
BID = '1002' and ID = 123 and L = 8
i hav put the index on this table as
1st index BID+ID+L
2ND INDEX AMOUNT+UPDATE
3RD INDEX AMOUNT1+UPDATE1
STILL SHOWS TABLE SCAN AVEN AFTER THE INDEXES
THIS QUERY UPDATE THOUSANDS OF DATA
December 25, 2008 at 11:30 pm
When <> (not equal to) condition is used table scan happens because all the records are to be compared.
December 26, 2008 at 8:19 am
Or's will also often cause table or index scans.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 26, 2008 at 10:22 am
samsql (12/25/2008)
hi i hav an update query which show table scan m confused how to i put indexes so that it does not scan the tableUpdate temp1 Set Date = NULL
Where (Amount <> Updated or Amount1 <> Updated1 or
Amount3 <> Paid) and Date is Not Null And
BID = '1002' and ID = 123 and L = 8
i hav put the index on this table as
1st index BID+ID+L
2ND INDEX AMOUNT+UPDATE
3RD INDEX AMOUNT1+UPDATE1
STILL SHOWS TABLE SCAN AVEN AFTER THE INDEXES
THIS QUERY UPDATE THOUSANDS OF DATA
I don't believe it's gonna matter what you do because you are comparing columns within the same table. In fact, the indexes probably aren't helping at all nor can they be made to do so, in this case. The good news is that these types of updates really fly... this one will probably handle a million rows in less than 7 seconds.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 26, 2008 at 12:29 pm
How much records are there in temp1 table and how much out of these are getting updated by this code? Also, for making use of the present indexes try to have one covering index then only I think that index will be considered by the optimizer.
MJ
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply