Re: Indexes and Stored Commands/Procedures

crunch wrote: > > General question on indexes. > > IMHO tables (with indexes) that have high volumes of insert and > delete operations need to be re-index periodically, else 'bad' things > will happen. Please comment. > With some databases (e.g. SQLBase 5.x), stored commands/procedures > need to be dropped and re-stored if you re-index a table. What are the > exceptions, if any? > I would appreciate any information on these subjects whether in > support or in contradiction of what I believe to be true. I have been > unsuccessful in trying to dig up literature that provides explicit > information on these matters which I need to build a case upon. Some of the > folks I work believe that re-indexing is strictly an option, not a > necessity and I have no way of proving otherwise. > Thanks much. > > Joe Yong > Software Alliance (M) Sdn. Bhd. Basically, making sure your index statistics and query plans are up-to-date is a necessity for anything other than very small tables. You can use "update statistics" and "sp_recompile" to do this. Or you can drop/recreate indexes and drop/recreate stored procedures if you prefer. The advantage of dropping/recreating indexes is that it will actually reorder the indexes (and table if there is a clustered index) and you can recover lost space due to fragmentation. In the case of dropping/recreating stored procedures, it can help minimize the "64K exceeded" problem where your procedure fails all of a sudden. In our case, we run "update statistics" and "sp_recompile" regularly (usually weekly, sometimes daily). We don't drop/recreate indexes or procedures regularly since the space issue is not an issue for us. Regards, Mike