Re: Indexing : Rules of Thumb ?

From: "edna.shepherd" <edna.shepherd@ntlworld.com> > >I have "inherited" a database with around 500 tables and hundreds of >indexes. >The majority of the tables are tiny in database terms (<1000 rows) but >still >have indexes applied; some of these tables will never grow beyond their >current volume. > >The database is almost certainly over-indexed and probably inefficient. >Any Rules of Thumb - in terms of numbers of rows - for when an index >becomes >more efficient than scanning ? > >Many thanks, > John I think you're confused: are you an Edna or a John? Anyway, as ever, it depends: are you doing DSS or OLTP? I'm guessing OLTP. What is your OPTCOMPIND set to in your onconfig? If it's set to 2, you may find that table scans on small tables lead to hash joins with bigger tables, and that can be very slow in an OLTP environment. It's also practically impossible to say which indexes are beneficial and which aren't up front. RULE NUMBER 1: If it ain't broken, don't fix it. _____________________________________________________________________________________ Get more from the Web. FREE MSN Explorer download : http://explorer.msn.com