varchar as primary key - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 32

Thread: varchar as primary key

  1. #11
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    That's a good logical reason to use a numeric PK, although it might need the varchar2 UK also and hence be less performant.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  2. #12
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by abhaysk
    Wat i gave the example there is just to illustrate the fact that insert or update with varchar as Pk would take more time...
    Abhay, no offence, but your example illustrates nothing whatsoever! You can't be serrious with your conclusion that the example you gave realy illustrates that updating varchar PK is slower than updating numeric PK! This would be simply too ridiculous. If I extrapolate from your childishly simpe example, what would you think of the following statement:

    "As abhay's example shows, it is perfectly evident, that creating an empty table with varchar column as a primary key is about 300 times slower than creating the table with number column as a PK. Just a look at the following timings from abhay's example prooves this point:

    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

    QUED."


    Would anyone seriously give any credibility to the above "proof"? Don't think so.

    If you would want realy to prove your point, then you should at least produce a representative test case. And I totaly agree with slimdave when he says that "it would be a real challenge to find any difference in performance inserting into a table with a varchar2 pk compared to number" (because that's exactly what you were claiming in your first reply).

    And BTW, your "logical" explanation why searching in character indexed column would be slower than searching in number indexed column only shows that you don't understand how oracle stores number datatype values in the database:
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #13
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Hooray for Jurij!
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  4. #14
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by jmodic
    Abhay, no offence, but your example illustrates nothing whatsoever! You can't be serrious with your conclusion that the example you gave realy illustrates that updating varchar PK is slower than updating numeric PK! This would be simply too ridiculous. If I extrapolate from your childishly simpe example, what would you think of the following statement:

    Would anyone seriously give any credibility to the above "proof"? Don't think so.

    If you would want realy to prove your point, then you should at least produce a representative test case. And I totaly agree with slimdave when he says that "it would be a real challenge to find any difference in performance inserting into a table with a varchar2 pk compared to number" (because that's exactly what you were claiming in your first reply).

    And BTW, your "logical" explanation why searching in character indexed column would be slower than searching in number indexed column only shows that you don't understand how oracle stores number datatype values in the database:

    First of all let me explain u how characters or numbers are stored at hard ware level ( I mean it hard disk level & not even OS level )

    Take for example a integer 0, it requires 4 bits to store ( as i said in my previous post & ther too i meant hard ware level and not OS or index level as these are logical rather than physical )......

    And that 4 bits would be '0000' as binay bit values.

    Now take how the character 'a' would be stored, it requires 8 bits & would be stored as ASCII equivalent 39 and in binary it would be '01101001'.

    So now for the CPU to compare the numeric digit 0 with 0 or 1 or so....lets assume it would take 'x' nano secs....

    So logically for the comparison of the character 'a' with 'a' or 'b' or so would rather take much time say 'y' nano secs
    and obivously 'y' is atleast 2 times the 'x'.

    Now lets come to logical storage as u mentioned the values of numbers & chars in indexes and so.......

    if u define char or varchar(2) then each character would require 1 byte to be stored at harware level.
    if u define number then each digit will be stored in mantisa and exponention form which would also require 1byte, due 1 mantisa part would require 4 bits and exponent would require 4 bits......if its 2 digit number then it would require 12 bits.......

    Now lets come to our question as to why Pk with varchar would be slower than Number or integer while insert or update.

    While insert on PK column oracle would search for the string, if its char type, if it existed in the table....may it search thru index it still need the help of CPU to comapre the String value......

    And as i explained above CPU wud atleast take time 2 times than if it were to compare with Number.......

    This will not be significant if the table were to have less records....but ofcource yes if it contained huge amount of data......

    My point was to show why char search takes more than number search.......and i hope this explains....


    Well i thought logically and not childishly.


    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"

  5. #15
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    well how about practically?

  6. #16
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    when u talk about practical things....it wud be noticable if table contains huge data.

    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. #17
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    no I mean if you have tested in real environment

  8. #18
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by abhaysk
    First of all let me explain u how characters or numbers are stored at hard ware level ( I mean it hard disk level & not even OS level )

    Take for example a integer 0, it requires 4 bits to store ( as i said in my previous post & ther too i meant hard ware level and not OS or index level as these are logical rather than physical )......

    And that 4 bits would be '0000' as binay bit values.

    Now take how the character 'a' would be stored, it requires 8 bits & would be stored as ASCII equivalent 39 and in binary it would be '01101001'.
    You are talking nonsence here. "Hardware level" (as you call it) doesn't store numbers or chars or dates or whatever - it only stores bits - zeroes and ones. That's all. When you store a data into the database, your OS or your controller or your disk doesn't know and doesn't care what kind of data you want to store - it simply writes the whole database block that DBWR has sent for write. So all the formatting of 0's and 1' in a database block is done by Oracle, and the database block is then simply broken down to multiple OS blocks which are written directly to disk, exactly the way as oracle has prepared them. And FYI, the number 0 stored in Oracle database doesn't occupy only 4 bits of your hard disk storage, it ocupies exactly 1 byte (8 bits). The number 1 will occupy 2 bytes, -1 will occupy 3 bytes, 999 will also require 3 bytes, while for example number 10000000000 will require only 2 bytes of your storage.
    if u define char or varchar(2) then each character would require 1 byte to be stored at harware level.
    Thats not true either. Each character stored in your database will occupy 1 or 2 bytes of your disk storage, depending on your database characterset.
    if u define number then each digit will be stored in mantisa and exponention form which would also require 1byte, due 1 mantisa part would require 4 bits and exponent would require 4 bits......if its 2 digit number then it would require 12 bits.......
    That's nonsence too. Read the previous explanation about how numbers are stored...

    While insert on PK column oracle would search for the string, if its char type, if it existed in the table....may it search thru index it still need the help of CPU to comapre the String value......

    And as i explained above CPU wud atleast take time 2 times than if it were to compare with Number.......
    Wrong conclusions due to previously explained misunderstandings. The time difference even on a big table would be negligible, I can assure you.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #19
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by jmodic
    You are talking nonsence here. "Hardware level" (as you call it) doesn't store numbers or chars or dates or whatever - it only stores bits - zeroes and ones.
    when did i say it stored numbers or char or so...
    if u read the post clearly i specifed the binary equivalent of number or char which wud be stored in the disk.

    So all the formatting of 0's and 1' in a database block is done by Oracle, and the database block is then simply broken down to multiple OS blocks which are written directly to disk, exactly the way as oracle has prepared them.

    S its done by oracle and it is following ASCII standards (one of character set example) .... that is wat i had given the conversion of the char 'a' and number 0, assuming its 1-Byte character set & ASCII conversion.
    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"

  10. #20
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Is there any difference in their impact on global warming?
    "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