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----- Sent: Thursday, April 18, 2002 10:27 AM To: [EMAIL PROTECTED]; Mercadante, Thomas F 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: Mercadante, Thomas F INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
