Re: VARCHAR2 PRIMARY KEYS

Not really answering your question, but here's something to consider. It may be more efficient to make the trip to the database to get the next sequence than to manage the assignment of Primary Keys yourself. The Primary Key value will eventually need to be placed in the database anyway, and this could be done when all the user-entered data for the record is ready for commital. The only time this would be less efficient is if you need to return the Primary Key value to the user before the record is committed.

The following is an educated guess regarding data type performance: I think the difference is negligible, as your keys will all be indexed anyway. If the key is short, I would think VARCHAR2 has the advantage, as only the exact number of characters in the key is read. In a number data type, the entire length of the data type would need to be read.

For example

VARCHAR2 (10)
Values starting at 1 incrementing by 1.
The first value is a 1, and only 1 character is read. At value of 1000, 4 characters are read, etc.

NUMBER(10)
10 characters are read every time.

So, if I am correct, it depends on your data, but the difference should really be nominal considering that the data is stored as bytes, not characters, and bytes are stored in blocks, and an entire block must be read to get the data anyway. Also, VARCHAR2 will ultimately take less blocks on the drive, so this would also increase access speed to a small degree.

"Stephen Chell" <slcnz1_at_yahoo.co.nz> wrote in message news:de67291b.0106211951.54d4dae4_at_posting.google.com...
> 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
> string, but I am concerned that this may have a significant impact on
> performance of complicated queries.
>
> Thanks in advance.
>
> Steve Chell
Received on Sat Jun 23 2001 - 14:44:34 CEST