Re: PK field - number of char

Brian,

Your figures for the number of bytest are incorrect. A 38 digit number (max Oracle can handle) takes approximately 20 bytes, a 38 character varchar2 takes 39 bytes.

To answer the original question: The only difference would be in converting the external format of the data (e.g. a double or int in the number case) to the internal format and in the space used; as soon as it is under the hood of Oracle both numbers and varchar2s are simply a variable length string of bytes, which needs to be saved in the index and compared. If your data really is numerical, you save space (and hence potentially some time inside the kernel) if it is stored as number but you pay with a potentailly somewhat slower conversion between internal and external format.

In practical terms, it probably doesn't matter enough to care, so your decision should be based on what you really have, i.e. numerical data should be stored in number, text data in varchar2.

Thanks, Bjørn.

On Thursday 18 April 2002 00:53, Brian Haas wrote:
> Tom,
>
> Well you got me sort of. I ran a few quick tests on a table with 500K
> rows. The return times were almost always identical. The main difference
> between a Pk with a number and a char/varchar is storage. a 40 digit
> number takes 4bytes of space. A 40 character string takes 10 bytes.
>
> This translated into the character datatype moving 30 more bytes per
> query over sql*net than the number datatype. On a heavily used
> application hitting a backend Oracle DB via sql*net, those extra bytes
> could make a difference in response time. but I guess that is more of a
> network bottleneck than a database one.
> here are my results:
> char(40) Pk:
> Elapsed: 00:00:00.52
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=10)
> 1 0 INDEX (UNIQUE SCAN) OF 'PKTEST_CHAR_PK' (UNIQUE) (Cost=2 C
> ard=1 Bytes=10)
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 3 consistent gets
> 0 physical reads
> 0 redo size
> 239 bytes sent via SQL*Net to client
> 253 bytes received via SQL*Net from client
> 3 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
> number datatype:
> Elapsed: 00:00:00.51
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=4)
> 1 0 INDEX (UNIQUE SCAN) OF 'PKTEST_PK' (UNIQUE) (Cost=2 Card=1
> Bytes=4)
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 3 consistent gets
> 0 physical reads
> 0 redo size
> 218 bytes sent via SQL*Net to client
> 246 bytes received via SQL*Net from client
> 3 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
> -Brian
>
> On Wed, 2002-04-17 at 11:52, Mercadante, Thomas F wrote:
> > All,
> >
> > Does anyone have any specific metrics demonstrating that a PK that is
> > based on a number field is faster than a PK based on a character field?
> >
> > I've seen it mentioned a couple of times today under the "Design
> > Question" topic.
> >
> > It doesn't make any sense to me that one or the other would be faster.
> > After all, we are talking about comparison searches within the B-Tree
> > index structure. Why searching down the tree for a number is any faster
> > than a char is lost on me.

-- 
Bjørn Engsig, Miracle A/S
http://MiracleAS.dk
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Bj=F8rn=20Engsig?=
  INET: bjorn_at_MiracleAS.dk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).