Re: Fast Re-add referential constraint

tgauchat@jps.net wrote: > > IDS 7.24.UC5 > > Is there a fast way to re-add a referential integrity constraint to a large > table? > > Currently the process takes 30 hours (several million row table). > > This is because the process re-verifies all the data. > > Can't the re-verify step be skipped? I just want the constraint for new data! Have you now re-read your question? A referential constraint is designed to maintain data integrity. You can't have integrity checked on only half your data. There is no way to skip the check of existing data. Why do you need to keep disabling and enabling? Is it for data loading? What about leaving the constraint in filtering mode with error? Cheers, -- Mark. +----------------------------------------------------------+-----------+ |Mark D. Stock - Informix SA http://www.informix.com |//////// /| |mailto:mdstock@informix.com http://www.informix.com/idn |///// / //| |http://www.iiug.org +-----------------------------------+//// / ///| | Tel: +27 11 807 0313 |If it's slow, the users complain. |/// / ////| | Fax: +27 838250 2325 |If it's fast, the users keep quiet.|// / /////| |Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////| +----------------------+-----------------------------------+-----------+

Mark D. Stock wrote: > > tgauchat@jps.net wrote: > > > > IDS 7.24.UC5 > > > > Is there a fast way to re-add a referential integrity constraint to a large > > table? > > > > Currently the process takes 30 hours (several million row table). > > > > This is because the process re-verifies all the data. > > > > Can't the re-verify step be skipped? I just want the constraint for new data! > > Have you now re-read your question? A referential constraint is designed > to maintain data integrity. You can't have integrity checked on only > half your data. > > There is no way to skip the check of existing data. Why do you need to > keep disabling and enabling? Is it for data loading? What about leaving > the constraint in filtering mode with error? I agree with Mark, why are you doing this? That said here is something else to help improve re-constraint performance: Create the supporting indexes for all primary and foreign key constraints manually before creating the constraints. The ALTER TABLE will use the existing index and in this way the primary key index of the referenced table and the foreign key index of the referencing table will be available to speed things a bit. Also you can then use the FILLFACTOR to build a more efficient index than the one created by the ALTER TABLE would be. In addition if you can live without droppingthese indexes during the load (or whatever) you will not have to wait for them to be rebuilt each time you readd the constraints. Art S. Kagel