Re: Most efficient datatype for keys???

Chrysalis wrote:
>
> 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.

If you were the computer, which would you rather do: 1. compare two integers
2. sequentially compare each byte of a string to another string?

   (which by the way must be converted to numbers first before     the comparison)

To me, you should NEVER choose varchar2 over a number for keys.

Try it yourself.
create two tables with each type of key, do some queries, time the results. numbers are the only way to go.

Randy :) Received on Wed Apr 09 1997 - 00:00:00 CDT