Yes, this was for Oracle. I was allowed to implement surrogate keys (partially). All tables have a surrogate key. Most tables have a natural key. Where there are tables that would normally be dependent of another table and the parent table has natural key I was required to migrate the natural key from all parents. In some cases I was also allowed to migrate the surrogate key from the parent for performance reasons.
In those tables where the natural keys were migrated these keys became alternate keys. In those tables with more than 3 columns in the alternate key performance has been degraded about 7 times. In other words a process that took 20 minutes when only using surrogate keys is now taking 140+ minutes now that I have added natural keys. Even when using the migrated surrogate key as an index for navigation. It is even worse without the navigation surrogate key. I could improve the performance of the process, but this is a process that will only run once or twice as it is a data migration process, from legacy to new db. John Davidson On Tue, Mar 16, 2010 at 2:05 PM, Corey Coogan <[email protected]> wrote: > Our DB is Oracle. Would this be the case for this situation? I'd > love for the answer to be yes. > > On Mar 16, 12:07 pm, John Davidson <[email protected]> wrote: > > Natural keys on some databases require a full table scan for retrieval, > > meaning that indexes are not used, resulting in slower performance on > data > > retrieval. Just putting a natural key with 3 or more columns in a table > may > > result in significantly reduced performance. > > > > John Davidson > > > > On Tue, Mar 16, 2010 at 1:01 PM, José F. Romaniello > > <[email protected]>wrote: > > > > > just in case you didn't see this before: > > >http://www.agiledata.org/essays/keys.html > > > > > 2010/3/16 Corey Coogan <[email protected]> > > > > > I'm trying to get my client to implement surrogate keys on their legacy > > >> database to make using an ORM more viable, among other reasons. I > drafted a > > >> quick document discussing the points and weighing surrogate keys > against > > >> natural keys. > > > > >> I'm hoping to get some feedback and comments on what's missing, > inaccurate > > >> or should be removed. Any help is greatly appreciated. > > > > >> Thanks, > > >> Corey > > > > >> -- > > >> You received this message because you are subscribed to the Google > Groups > > >> "nhusers" group. > > >> To post to this group, send email to [email protected]. > > >> To unsubscribe from this group, send email to > > >> [email protected]<nhusers%[email protected]> > <nhusers%[email protected]<nhusers%[email protected]> > > > > >> . > > >> For more options, visit this group at > > >>http://groups.google.com/group/nhusers?hl=en. > > > > > -- > > > You received this message because you are subscribed to the Google > Groups > > > "nhusers" group. > > > To post to this group, send email to [email protected]. > > > To unsubscribe from this group, send email to > > > [email protected]<nhusers%[email protected]> > <nhusers%[email protected]<nhusers%[email protected]> > > > > > . > > > For more options, visit this group at > > >http://groups.google.com/group/nhusers?hl=en. > > > > > > -- > You received this message because you are subscribed to the Google Groups > "nhusers" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]<nhusers%[email protected]> > . > For more options, visit this group at > http://groups.google.com/group/nhusers?hl=en. > > -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
