| The system can’t know what conditions will be present when your query executes sometime in the future without defining those conditions. To define those conditions you create a simulated environment with the index, data, and load you want to test and test it. Without more info, your requirements and constraints don’t make any sense. So, no there is no magic wand that can be waved to indicate whether or not your query will magically be faster or slower at some time in the future, with some unknown data, under some unknown db load. (I know that’s not what you intended to ask, but it is not clear why you are asking what you are asking.) If you are working on a live production system (which I do understand your reluctance to create indexes for testing) , then you would be better off creating a duplicate system and testing the queries and indexes on it. If you are running on a development system with only sample data, then the analyzer won’t give you good info anyway. Why not just create the indexes/data and evaluate the result in a simulated system? If you read and have a little understanding how the planner works and how statistics are generated, you can get usable info about indexes and relative performance in pretty simple simulated environments. Now, i’m just guessing, but you may be thinking that this simulation business is a lot of work. You could be right, which is why optimization often occurs after specific problems are identified rather than as an earlier intellectual exercise. Most people would just use general rules of thumb or their experience to create indexes until specific performance indicated something else is needed. Then they would solve the specific performance issue. Neil
|
