Re: Surrogate numeric keys versus natural character keys.

Alan Byrne wrote:
>
> Hi there,
> I have a question in the area of DB design which impacts on
> performance.
>
> Lets say we have a table containing customer information as follows
>
> CREATE TABLE cust
> (
> cust_number VARCHAR2(30),
> cust_name VARCHAR2(100),
> CONSTRAINT cust_pk PRIMARY KEY (cust_number)
>
> )
>
> and an address table thus:
>
> CREATE TABLE addresses
> (
> cust_number VARCHAR2(30),
> address_seq NUMBER(5),
> full_address VARCHAR2(200),
> CONSTRAINT address_pk PRIMARY KEY (cust_number, address_seq),
> FOREIGN KEY (cust_number) REFERENCES cust(cust_number
> )
>
> And let's say that many other tables have a foreign key which
> references
> the cust_number. How much of a performance benefit is gained by
> introducing a surrogate numeric key (cust_id) and using this in all
> the
> other tables for foreign keys? Oracle uses UNICODE to encode
> characters,
> so that means 16-bits per character, right? Assuming an 8-letter
> customer
> number, the comparison would be between two 96-bit fields. Whereas if
> there was a surrogate numeric key, also 8 digits, the comparison would
>
> only require 24-bits. I know this is a bit of an ill-defined question,
>
> but, given these parameters, how much of a performance benefit will a
> surrogate numeric key give, and is there any web-page which discusses
> these considerations?
>
> Thanks for the help,
> Alan.
>
>

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

Good things with synthetic keys
- you can use reverse indexes
- as levels of subordinates grow, the primary key indexes will be smaller on the subordinates
- you can update the 'real' key

Take your pick...

HTH

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

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