Re: Large, but empty tables

> > per Doug Rudoff: > > I still don't understand why there is not in Informix a single command > to clean-up and resize a table. > > Amen - me either. Do other databases have this problem? > > Bill Ross > Ingres does have such a command, 'modify', which rebuilds a table or an index from scratch. For Btree-based tables (like Informix clustered tables, it has an option called 'modify to merge' which doesn't do a complete rebuild but simply squeezes out Btree index entries for deleted rows. Oracle and Sybase don't have any equivalent commands that I'm aware of. Having this command is very useful -- however, it's even more useful not to need it at all! Ideally, Btree structures should be updated sufficiently completely that there is no need to rebuild tables and indexes periodically. This becomes critical when you have to provide 24 hour database service - online backups are quite common now, but no-one has yet addressed the issues of index rebuilding and of gathering optimiser statistics. This prompts a question on a related topic: does Informix (OnLine or SE) lock out tables or the database when gathering statistics for the optimiser? Richard =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Richard Donkin Hoskyns Open Systems Division Internet: richardd@inset.co.uk 190 City Road, LONDON EC1V 2QH UUCP: ...!mcsun!ukc!inset!richardd United Kingdom Fax: +44 71 251 2853 Tel: +44 71 251 2128