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.