> > > > Similarly using the no. of select hits on a table we can check that if >> maximum no. of times it is on a non-index field we can index on that field >> to make select faster. >> > > It's impractical to figure out where indexes should go at without > simulating what the optimizer would then do with them against a sample set > of queries. You can't do anything useful just with basic statistics about > the tables. > > I would recommend > http://msdn.microsoft.com/en-us/library/aa226167(SQL.70).aspx<http://msdn.microsoft.com/en-us/library/aa226167%28SQL.70%29.aspx>as > a good, practical introduction to the topic of what it takes to figure > out where indexes go at, from someone who came up with a reasonable solution > to that problem. You can find a list of the underlying research they cite > (and an idea what has been done since then) at > http://portal.acm.org/citation.cfm?id=673646 > > Even if you have devised a way to find the appropriate set of indexes, just have a index adviser, which would advise a set of indexes for a set of queries and let the DBA and the application user take the final call, after looking at them..
Gokul.
