October 9, 2007 at 8:04 am
HI ALL FUNDIS
CAN SUM ONE PLEASE EXPLAIN TO ME HOW DOES QUERY OPTIMIZER WORKS
I MEAN WHY DOES IT TAKE CERTAIN WAY TO ACHIVE RESULT.
WHAT MAKES IT TAKE THAT PARTICULAR WAY.
I KNOW FROM THE BOOKS IF YOU HAVE WHERE ,JOIN,CLUSE YOU NEED INDEXES.
IF YOU REQUIRE EXACT MATCH YOU SHOULD USE NON- CLUSTERED INDEX .
FOR RANGE VALUES USING BETWEEN AND SORTED RESULT USE CLUSTERED INDEX.
BUT I STILL DONT UNDERSTAND HOW DOES QUERY OPTIMIZER WORKS.
PLAIN ENGLISH PLEASE I AM NEW AT THIS STUFF.....THANKS
DA MAN FROM AFRICA
October 9, 2007 at 8:28 am
First, please don't post in all CAPS.
There's no easy way to explain this. The query optimizer runs through a number of plans and tries to guess which indexes or joins might perform best, then it picks one. It's a complex process and if you really want to know the details, pick up one of the Storage Engine books.
Your general rules of thumb make sense. For OLTP systems, use 5-10 indexes only. More and you're adding lots of overhead with each one. For more read-only, OLAP type systems, you might have dozens of indexes.
If you have specific questions or wonder why something happens, please post that. There's no good way to answer a general question like "How does the query optimizer work?"
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply