Re: Performance of constraints

On Jan 17, 7:22am, Uwe Droste wrote: > Subject: Performance of constraints > Hello world, > > we have a problem with INFORMIX CONSTRAINTs. > INFORMIX automaticially create indices for foreign keys, > which don't have an index (here terms_of_delivery). So > we have in this example one table (orders_t) with one > primary an many foreign keys. We only want one Index on > the primary key to have a very good performance on INSERT > and UPDATE. Now we have many Indices on this table and a > very bad performance. > > So, have anyone an idea to create CONSTRAINTs without > creating an index? > > Thank you > Uwe The problem is that constraints are two ended. Not only are you creating a reference from the child to the parent but you are also enforcing rules on updates and deletes on the parent. A referential constraint implies that if a delete is attempted on a parent row that has existing child rows the delete is to be rejected. Now consider the performance of that parent delete if no index existed on the child. It would have to do a sequential scan of the child table, this could take hours, so indexes are required on the child tables so as to ensure reasonable performance for both ends. If you don't need to enforce parent rules but only need to ensure that a parent exists at the time a child is inserted/updated then I suggest using a trigger rather than a referential constraint. If the parent rules need to be enforced then you must either have the referential constraint or you can use a trigger. The only advantage of a trigger is that it may be able to use an existing performance index of combined attributes where the first attributes of the index are the same and are in the same order as the foreign key. A referential constraint will build its own index rather than use this index which is a reported bug. You can use ERwin to help you build these referential checking triggers. Cheers - Jim -- ----------------------------------------------------------------------------- Jim Gordon DHL Airways Inc. jgordon@us.dhl.com ----------------------------------------------------------------------------- My opinions are my own. They may vary with time but they remain mine!