RE: Space Recalamation

Esteban opined: > If the table is going to be deleted I would use drop > table instead of delete and alter frag or truncate and > alter . One reason for deleting and using the alter fragment is foreign keys. Dropping the table means you drop all its indexes and all of the foreign keys which reference its unique constraints, also. I know this seems odd to those of us who manage all of our data and schema elements so well, but there exist some Database Administrators who do not have the schema information at their fingertips. It may not be convenient for them to rebuild all the referential constraints. That said, I usually drop and recreate the table, also. However, I have learned why it is best not to assume that Informix Best Practices are universal or even particularly widespread. Sincerely, Christopher Coleman Steering Committee President Kansas City Informix Users Group www.iiug.org/kciug Database Analyst Pharmacy Division Mediware Information Systems, Inc.

When you drop and recreate a table you also drop all views on the table, so you need to save all views on the table. To get all views and tables with references you may use the below SQL statements. ----------------------------------- select t.tabname from systables t, sysreferences r, sysconstraints c where r.constrid= c.constrid and t.tabid = c.tabid and r.ptabid = (selecttabid from systables where tabname = 'bla'); select t.tabname from systables t wheret.tabid in (select dtabid from sysdepend where btabid = (select tt.tabid from systables tt where tabname = 'bla')); --Replace 'bla' with the name of the table you want to --drop and recreate) ---------------------------------------- Then safe the schema of these views and tables , so you will be able to recreate the views and references later. Best regards Tilman -----Urspr|ngliche Nachricht----- Von: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] Im Auftrag von Christopher.... Gesendet: 21 November 2005 20:50 An: ids@iiug.org Betreff: RE: Space Recalamation [6028] Esteban opined: > If the table is going to be deleted I would use drop > table instead of delete and alter frag or truncate and > alter . One reason for deleting and using the alter fragment is foreign keys. Dropping the table means you drop all its indexes and all of the foreign keys which reference its unique constraints, also. I know this seems odd to those of us who manage all of our data and schema elements so well, but there exist some Database Administrators who do not have the schema information at their fingertips. It may not be convenient for them to rebuild all the referential constraints. That said, I usually drop and recreate the table, also. However, I have learned why it is best not to assume that Informix Best Practices are universal or even particularly widespread. Sincerely, Christopher Coleman Steering Committee President Kansas City Informix Users Group www.iiug.org/kciug Database Analyst Pharmacy Division Mediware Information Systems, Inc.