Re: Performance of constraints

On Jan 19, 5:06pm, Mike Reed - Radiix wrote: } Subject: Re: Performance of constraints } > } >You can use ERwin to help you build these referential checking triggers. } > } >Jim Gordon DHL Airways Inc. jgordon@us.dhl.com } >----------------------------------------------------------------------------- } >My opinions are my own. They may vary with time but they remain mine! } } Forgive me if this doesn't address the primary question, but } I thought I would add it to the discussion of RI. } } There are at least three reasons to NOT create referential integrity } via create table statements as ERWIN does. } } First, if you explicitly create the indexes, and then add the constraints, } the existing indexes will be used. Just because you may want to drop } RI at some point doesn't mean that you necessarily want the indexes to go } away as well. Indexes will be dropped with the constraint if they were } created implicitly. :-( } } Second, should you fragment your tables, you will probably want to } create your indexes explicitly and even detach them from the table. } You can't do that implicitly. } } Third, implicitly created indexes cannot be altered to cluster. } } Michael Reed, Radiix Inc., Ann Arbor, MI All the above is true except that you can use ERwin to generate the schema and have constraints use indexes defined in the ERwin model. On the schema generation report you use the ALTER/PK and ALTER/FK schema generation options. This moves the creation of PK and FK constraints to the end of the script and creates them using ALTER statements. This way they get to use any explicitly defined indexes that have been created in the ERwin model. Just remember that FK constraints will use only indexes that exactly match the FK. It won't use combined indexes that have the FK as the first part of the index, even though it should. 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!