Re: VARCHAR2 PRIMARY KEYS

In article <de67291b.0106221525.29f7ae8e_at_posting.google.com>, slcnz1_at_yahoo.co.nz says...
>
>I need to find out how the performance of varchar2 primary keys
>compares with numeric primary keys when joining multiple tables
>together in a complex query.
>
>Is there a significant degredation in performance?
>
>If anyone can speak from first hand experience, or even better, point
>me in the direction of an authoritative article, it would be much
>appreciated.
>
>Background: We are in the design stage of a large web application.
>One of the decisions that will soon be made is whether or not to
>generate primary keys in the middle tier (thereby by eliminating a
>trip to the database to fetch the next value from a sequence). If
>this approach is taken the generated primary key will probably be a
>36 byte string, but I am concerned that this may have a significant
>impact on performance of complicated queries.
>
>Thanks in advance.
>
>Steve Chell

numbers will be a tad bit faster as they tend to be smaller strings to compare (oracle numbers are a varying length string of bytes -- not fixed length fields).

In ANY case, why would you require a round trip to the server to use a sequence???

  insert into t ( pk, c1, c2, ... ) values ( seq.nextval, ?, ?, ... );

and if you needed to know the value of the PK

  begin
    insert into t ( pk, c1, c2, ... ) values ( seq.nextval, ?, ?, ... )     returning pk into ?;
  end;

and prepare and execute that -- or just use seq.currval in sebsequent statements that needed the current value.

DO NOT have the middle tier generate them. You'll be making your own mini-database in the middle. What happens when you want to have many app servers making them all at the same time??

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp