Re: PK - Character vs. Numeric

Walter K wrote:
>
> I'm sure this has been raised in the past, but...
>
> Is it better, in terms of performance, to use numeric
> primary keys versus character/string keys? It is my
> understanding that this is really a space-savings
> issue rather than a performance issue.
>
> Can someone elaborate more on this?
>
> Thanks.
> -W
>

Walter,

   Space and performance are closely related. Remember that your blocks are of a fixed size. If you pack a huge number of 'things' (whatever 'thing' is - row, index entry, whatever) into one block, it means that this block will somehow be the entry point to twice as many rows as a block containing half the same number of 'things'. It sounds logic. CPU-wise and I/O-wise, only the size of the block counts. In other words, if you pack twice as many things in a block, the same 'effort' will allow you to process twice the number of rows. Put it otherwise, if you manage to store references to the same number of rows into half the number of blocks, you will need half the CPU and I/O to get it; not very far from saying that it will be twice as fast (OK, this is a crude first order approximation). This is of course mainly true for SELECTs (which usually represent 70/80% of statements), since you may have more contention problems with UPDATEs, INSERTs and DELETEs. But you get the idea.

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

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).