Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: PK field - number of char

Re: PK field - number of char

From: Jared Still <jkstill_at_cybcon.com>
Date: Thu, 18 Apr 2002 07:43:35 -0800
Message-ID: <F001.00447FE1.20020418074335@fatcity.com>

Tom,

If you're keeping up on the other threads, you will see that we are in fact in complete agreement.

As for the "char vs. number , which is faster in an index" debate that arises from time to time, I personally think it's a silly waste of time.

No disrespect intended, maybe it's the first time you've seriously considered it. Folks that worry about the the nanoseconds they may be wasting by using the 'wrong' one are being penny wise and pound foolish.

Much greater gains are to be made elsewhere in every application.

Just my shillings worth,

Jared

On Thursday 18 April 2002 07:37, Mercadante, Thomas F wrote:
> Jared,
>
> I disagree. In some cases, I would support and use natural values for
> Primary keys.
>
> In the case of State Codes, County Codes, Yes/No codes and other that are
> too obvious, I really do not see the value of using an sequence number for
> the PK.
>
> I have a YES/NO table in my database. The Web developers use a drop-down
> field to allow the users to select the value they want (YES or NO). If the
> developers were required to to support the sequence number, it makes the
> coding a tiny bit more complicated (obviously, you and I can think of
> dozens of ways to make it insignificant).
>
> I guess I'm thinking that this is one of those personal preference things.
> My original question was looking for a good reason why I should NOT use
> chars in an index (thus forcing me to always use a sequence as the PK). So
> far, I see no reason not to .
>
> See ya.
>
> Tom Mercadante
> Oracle Certified Professional
>
>
> -----Original Message-----
> From: Jared Still [mailto:jkstill_at_cybcon.com]
> Sent: Thursday, April 18, 2002 10:27 AM
> To: ORACLE-L_at_fatcity.com; Mercadante, Thomas F
> Subject: Re: PK field - number of char
>
>
>
> Tom,
>
> If you are generating keys as you should be, they will be numeric.
>
> Jared
>
> On Wednesday 17 April 2002 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.
> >
> > Just curious if anyone has a reference someplace pointing this out.
> >
> > Thanks
> >
> > Tom Mercadante
> > Oracle Certified (Stupified today) Professional
> >
> >
> > -----Original Message-----
> > Sent: Wednesday, April 17, 2002 2:21 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > If you go with the first option, you will likely be able to get out of
> > joining your STATE table to the referencing tables in a bunch of cases
> > (since the 2-letter abbreviation is interpretable on its own). But if
> > you'll wind up having to do the join anyway (e.g., to display the
> > STATE_DESC) then those joins will likely be faster on a numeric...
> >
> > HTH,
> >
> > -Roy
> >
> > Roy Pardee
> > Programmer/Analyst
> > SWFPAC Lockheed Martin IT
> > Extension 8487
> >
> > -----Original Message-----
> > Sent: Wednesday, April 17, 2002 10:19 AM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > To simplify my question, if I am creating a STATE table to hold all the
> > states of the US, should I create it like this...
> >
> > Name Null? Type
> > ----------------------------------------- --------
> > ----------------------------
> > STATE_CODE NOT NULL CHAR(2) <-- PK
> >
> > STATE_DESC NOT NULL VARCHAR2(50)
> >
> > or like this...
> >
> > Name Null? Type
> > ----------------------------------------- --------
> > ----------------------------
> > STATE_ID NOT NULL NUMBER <-- PK
> > STATE_CODE NOT NULL CHAR(2)
> > STATE_DESC NOT NULL VARCHAR2(50)
> >
> > I'm trying to figure out which is more efficient, STATE_CODE or STATE_ID,
> > when doing a PK lookup, dealing with FKs, etc.
> >
> > Many TIA!!!
> >
> > Chris

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.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).
Received on Thu Apr 18 2002 - 10:43:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US