Re: Most efficient datatype for keys???

Tim Witort wrote:
>
> Z. Martinez wrote:
> >
> > I'm trying to determine the fastest datatype for a key?
> > I will be using it as a primary key for a very large table.
> >
> > I'm currently using a number(10).
> > Is there a more efficient datatype I can use for this purpose.
> >
> > Thanks in advance.
> >
> > Please respond to zlm101_at_psu.edu
>
> The smallest column type which will allow you to uniquely
> code every row is about the best you can do. If you will
> have no more than 9,999,999, then a NUMBER(7) might work
> (assuming there is no loss of ID usage from deletions and
> subsequent inserts). Your NUMBER(10) will allow ten billion
> rows (now *that's* a BIG table).
>
> But I really doubt that you will get a significant performance
> boot or drag by changing the key column by a few bytes.
> As long as the resulting key is unique and has an index on
> it... should be plenty snappy.
>
> -- TRW
> --
> |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
> | Tim Witort || He is not silent. He is not |
> | trw_at_medicalert.org || whispering. We are not quiet |
> | || we are not listening. |
> | Pin: TZ, only one :^( || - Out of the Grey |
> |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
   

   It also depends on what you mean by _efficient_.    Numeric keys must *always* be converted to/from a different character set on output/input (since Oracle's number format is unique). This imposes a CPU overhead which, measuring FETCH times *only*, is of the order of 30%-80% over the fetch time for a varchar2 column..

   True, a numeric value greater than 10000 can be stored in fewer bytes than a varchar2 representation of the same number, but I would *always* choose varchar2 for a primary key, even for a _numeric_ domain of values. (It would be extremely unusual for a primary key to require numeric operations to be performed on it).

Chrysalis. Received on Wed Apr 09 1997 - 00:00:00 CDT