Re: Surrogate numeric keys versus natural character keys.

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: 2000/08/09
Message-ID: <965840496.19631.3.pluto.d4ee154e@news.demon.nl>

First of all, please do not post in HTML. Many people not using Outlook will not be able to read it. You might get flamed the next time.

That said,
I assume the cust number is numeric. You should *never* store a true numeric in a varchar2. One reason not to do this is you may easily not remember it is a varchar2, use a numeric variable instead and cause all kind of implicit conversions.
Also the variable length comparison semantic are ill-suited to numbers. IMO the comparison semantics in numbers are far more efficient than the comparison semantics for strings.
You are going to use those string comparison semantics for all your index lookups etc, etc, etc.
Also, my experience is varchar data usually hardly has uniform distribution, resulting in steeper indexes, taking up more space. Finally, you are not right in stating Oracle is using unicode. It will do so only when you explicitly are using unicode as database characterset. I assume unicode is most used in the Asia Pac region, I -based in the Netherlands- never needed to use it. The only advantage of not using a surrogate is the non-surrogate can be updated more easily, however usually people prefer to delete and insert anyway.

Hth,

Sybrand Bakker, Oracle DBA

  "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 Wed Aug 09 2000 - 00:00:00 CDT