Re: Surrogate numeric keys versus natural character keys.

From: Van Messner <vmessner_at_bestweb.net>
Date: 2000/08/12
Message-ID: <ofgl5.710$x16.44186@monger.newsread.com>

It's a little late for this thread but I just saw the question. Often a surrogate key is provided from a sequence. If you are going to be doing replication on your database, read the manual about how sequences are handled. This could have a bearing on whether you want to use sequence driven keys.

Van
  "Alan Byrne" <albyrne5_at_hotmail.com> wrote in message news:3991847F.85B3B5DC_at_hotmail.com...
  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.          

--

Received on Sat Aug 12 2000 - 00:00:00 CDT