Re: Most efficient datatype for keys???

Randy DeWoolfson wrote:
>
> 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 :)

Randy,

I just completed two years at Oracle, and I was as suprised as you were to hear from some of the leading Oracle Benchmarking folks that char is indeed faster than integer on primary keys in Oracle. I also recall reading in older Oracle 7.0 or 7.1 docs that integer was faster. But the Server Technologies guys doing the benchmarks stood fast on this when I pressed them.

Chrysalis I believe is right.

-- 


Robt

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^~~~~....
Robert Miller         

Oracle Master DBA  
Inovative Information Systems Inc. 

robt.miller_at_airmail.net
(214) 532.6558

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^~~~~....
Any opinions expressed are my own and do not necessarily
represent any employer.