Rebuilding Indexes and Recovering Space
I am new to Informix, having mis-spent (in this forum's eyes) the last dozen and more years working with Brand O and Brand MSS. I have been tasked with rebuilding indexes on one of our databases. I have spent a significant amount of time looking for the simple equivalent of Brand O's 'rebuild index' command or Brand MSS magic button. In searching this archive, I found several references to 'set indexes for table <fred> disable' and '...enable' that appears to be the solution I have been looking for (I am not going to quibble about two lines versus 1, as long as I don't have to research the 'create index' statement.) My question is: will using the set disable/enable pair recover dead space from the index that may remain from massive deletes from it's tables? Or this this automagically handeld by the special 'Btree Cleaning Thread' generated by 'Update Statistics'? or do I need to drop and recreate the index(es)? If you want to email me as well as post, please use jmaloney@npc.net for this effort. -- Joseph R.P. Maloney, CCP,CSP,CDP MPiR, Inc. 502-451-7404 some witty phrase goes here, I think. Sent via Deja.com http://www.deja.com/ Before you buy.
Joe Maloney wrote: > I am new to Informix, having mis-spent (in this forum's eyes) the last > dozen and more years working with Brand O and Brand MSS. Well, at least you made it! > I have been tasked with rebuilding indexes on one of our databases. I > have spent a significant amount of time looking for the simple > equivalent of Brand O's 'rebuild index' command or Brand MSS magic > button. > > In searching this archive, I found several references to 'set indexes > for table <fred> disable' and '...enable' that appears to be the > solution I have been looking for (I am not going to quibble about two > lines versus 1, as long as I don't have to research the 'create index' > statement.) > > My question is: will using the set disable/enable pair recover dead > space from the index that may remain from massive deletes from it's > tables? Or this this automagically handeld by the special 'Btree > Cleaning Thread' generated by 'Update Statistics'? or do I need to drop > and recreate the index(es)? disable/enable is functionally equivalent to drop/create. Update Stats does do stuff with Indexes. However, my personal experience is that there nothing as "refreshing" as an index rebuild. This is particularly true for table that have significant inserts & deletes (more than 20% per week of the average number of rows). Rudy
Hi, it should also be possible doing a create CLUSTER index on a normal index of the table, that's sorting physically the .dat file in the order of this index. Und after any insert in that table it is autmatical a normal index! So, if it is a table with no or very low inserts and looking for a good index (if there are more) it can improve the performance too. Cheers Peter > > >I am new to Informix, having mis-spent (in this forum's eyes) the last >dozen and more years working with Brand O and Brand MSS. > >I have been tasked with rebuilding indexes on one of our databases. I >have spent a significant amount of time looking for the simple >equivalent of Brand O's 'rebuild index' command or Brand MSS magic >button. > >In searching this archive, I found several references to 'set indexes >for table <fred> disable' and '...enable' that appears to be the >solution I have been looking for (I am not going to quibble about two >lines versus 1, as long as I don't have to research the 'create index' >statement.) > >My question is: will using the set disable/enable pair recover dead >space from the index that may remain from massive deletes from it's >tables? Or this this automagically handeld by the special 'Btree >Cleaning Thread' generated by 'Update Statistics'? or do I need to drop >and recreate the index(es)? > >If you want to email me as well as post, please use jmaloney@npc.net >for this effort. >-- >Joseph R.P. Maloney, CCP,CSP,CDP >MPiR, Inc. >502-451-7404 >some witty phrase goes here, I think. > > >Sent via Deja.com http://www.deja.com/ >Before you buy.