varchar as primary key
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 32

Thread: varchar as primary key

Hybrid View

  1. #1
    Join Date
    Aug 2002
    Posts
    115

    varchar as primary key

    hello ppl,

    Are there any kind of disadvantages keeping a varchar column as your primary key?
    Thanks

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Performance might slightly get affected during insert.

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Aug 2002
    Posts
    115
    thanks abhay

    So it would even effect the performance of tables referencing this column , wouldnt it?

  4. #4
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    Originally posted by aspdba
    thanks abhay

    So it would even effect the performance of tables referencing this column , wouldnt it?
    It is otherway around this time...
    -nagarjuna

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I bet it would be a real challenge to find any difference in performance inserting into a table with a varchar2 pk compared to char, number or date. If the best data type for the column that is the PK happens to be varchar2, then use it.

    There are plenty of other issues to think about in database design -- forget about this one, it's a irrelevance.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by slimdave
    I bet it would be a real challenge to find any difference in performance inserting into a table with a varchar2 pk compared to char, number or date.
    Slimdave:

    It does matter, if we think logically searching a character would result slower than seraching number. One Character will use 8 bits to be stored on harddisk and number would use 4 bits.
    During insert or update oracle has to first search if the string exists in that column so goes if the column is as number & obivously searching a number is much faster than searching a string...

    consider a example..

    Code:
    SQL> create table test1 ( Emp_Name Varchar(8) primary key );
    
    Table created.
    
    Elapsed: 00:00:03.08
    SQL> create table test2 ( Emp_Number Number(8) primary key );
    
    Table created.
    
    Elapsed: 00:00:00.01
    
    
    -- Added some 20 distinct records of 8 charactered value in table test1 & 8 digited value in test2......
    
    
    SQL> update  test1 set emp_name='AbhaySK1' where emp_name='AbhaySK2';
    update  test1 set emp_name='AbhaySK1' where emp_name='AbhaySK2'
    *
    ERROR at line 1:
    ORA-00001: unique constraint (ABHAY.SYS_C001262) violated
    
    
    Elapsed: 00:00:00.03
    
    
    SQL> update test2 set emp_number = 44444444 where emp_number = 22222222;
    update test2 set emp_number = 44444444 where emp_number = 22222222
    *
    ERROR at line 1:
    ORA-00001: unique constraint (ABHAY.SYS_C001263) violated
    
    
    Elapsed: 00:00:00.00
    
    -- Searching a number will be faster then Characters & thas obivious.
    it might appear that difference of .03 secs is not a big deal...yes it is not coz u have jus some 20 records so is fast......had u had some 10 million records then this would have gone in may be mins.....



    There are plenty of other issues to think about in database design -- forget about this one, it's a irrelevance.
    It wud be of importance if he were to have huge tables with crores of records....

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Is that what passes for a real test nowadays, then? I think not. Don't show me a 0.03 sec walltime difference on a constraint violation inserting into a twenty row table, then extrapolate it it to 10 million rows, do a real test. Are you teling me that an application is going to try inserting 10 million rows PK values, all the same, into a twenty row table?

    If it makes logical sense for the PK to be numeric, store it as numeric. If it needs to be varchar2, then store it as that.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    slimdave:

    now the discussion is diverting....the question was not if its logical to have PK of varchar or number....

    Question was Whether PK of varchar wud hinder the performance...

    and obiviously yes....as compared to PKs with numbers...

    Wat i gave the example there is just to illustrate the fact that insert or update with varchar as Pk would take more time...

    And s ....u can logically comprehend that by extrapolating the graph (from above example)....with many records, say 1 lackh, in table & trying to update/insert ...u will suerly have to wait for say 30secs to 1 row to be updated/inserted sucessfully.

    If it were a numeric type , i bet , it wud jus take 2 to 3 secs to update/insert with same number of recs as above..i.e 1 lackh..

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Your demonstration has no meaning whatsoever, so extrapolating it is a waste of time. A wall clock measurement of 0.03 seconds, vs 0.00 seconds, tells us nothing at all.

    Firstly, measure how long it takes to successfully insert a row in CPU time -- not wall clock time, not PK violation.

    Then take account of the need to actually store text. I don't believe that any one would seriously consider storing a numeric PK in a varchar2 column, so the need must be to store a unique text string, right? If you do that with a numeric PK you must be talking about a synthetic PK, so the test case ought to be ...

    create table test1 ( Emp_Number Number primary key ,Emp_Name Varchar2(8) Unique);

    ... and ...

    create table test2 ( Emp_Name Varchar2(8) Primary Key);

    Test inserts into those structures.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    Re: varchar as primary key

    Originally posted by aspdba
    Are there any kind of disadvantages keeping a varchar column as your primary key?
    Yes, it might tempt you to give meaning to the key. I'm a strong believer in meaningless PK's (hence use a sequence).
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width