Re: Surrogate numeric keys versus natural character keys.

Alan Byrne wrote:
>
> Hiya Connor,
> thanks for the reply. I still have a couple of questions though! :)
>
> Connor McDonald wrote:
>
> > Bad things with synthetic keys
> > - you typically need to index the 'real' key anyway so updates are
> > slower.
> > - the 'real' will not be contained in subordinate tables
>
> I can live with both of these side-effects if there is a significant
> performance benefit when performing complex selects from multiple tables
> which join to the customer table. Where can I web page which discusses
> these kinds of performance issues?
>
> > Good things with synthetic keys
> > - you can use reverse indexes
>
> What is a reverse index? (You'd never guess I wasn't a DBA would you?
> :))
 

> > - as levels of subordinates grow, the primary key indexes will be
> > smaller on the subordinates
>
> This is assuming you use a surrogate/synthetic key at each level of
> subordination, right?
>
> > - you can update the 'real' key
>
> I guess it's really just the performance end of things that I am
> interested in, I was pretty much aware of most other benefits (though
> I'd never heard of a reverse index).
>
> Thanks for the help,
> Alan.

"I can live with both of these side-effects if there is a significant  performance benefit when performing complex selects from multiple tables
 which join to the customer table. Where can I web page which discusses  these kinds of performance issues?"

As a general rule, I've always been pretty impressed with join performance
in Oracle, so using surrogate keys (and thus joining back to the parent table for get key information) I don't think is a major problem here

"What is a reverse index? (You'd never guess I wasn't a DBA would you?"

If you've heavy inserts on a surrogate key, a lot of people can be competing
for the "right hand end" of the index structure as the index grows. A reverse
index reverses the bytes as the index is built...Thus the key values are dispersed throughout the tree. The other that I consider a benefit is people
often start to place interpretations on surrogate keys that they shouldn't. The
reversing you could say is a "randomiser".

"This is assuming you use a surrogate/synthetic key at each level of subordination, right?"

Yup.

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse