Re: Is the use of VARCHAR(256) as Primary Keys preferred in Oracle?

vendor should be using varchar2(xx) not varchar. You can use varchar2 as a primary key value and it should work fine. I would be surprised if the key value is 256 characters. That is fairly large for a key. Sounds like the vendor has defined the key large just to have a few predetermined sizes. (eg 256 should be large enough) Varchar or varchar2 is stored as a variable length so if just 12 characters are used then only 12 are stored. Hence for an index on the key the index is going to be as large as the actual data (not the max size unless the data is the max size). The smaller the key the more keys can be in a database block. So if the actual size is 100 bytes then fewer keys per block will be stored vs a key size of 10 bytes. Smaller key size means less IO to use the index. (more effecient)

So I don't see anything wrong using varchar as a key ( I do that myself), but 256 sounds like an unusually large key size. You could do:

select max(vsize(key_column)) from a_table ; and that would tell you the current max size of the key in the table. (key_column being the column in question and a_table being whatever table you want to inspect.) I would hope that 256 is no the actual key size. (much smaller)

Maybe they are using GUIDs for primary keys. (especially if it is a Windows app.) My first thought would be UGH. I haven't benchmarked it, but I would think that would be a lot slower than a sequence number for a primary key. GUIDs being this long string of characters and sequence numbers being (even converted to characters) a much shorter string of characters.

Jim

"Russ Clancy" <rclancy_at_rotech.com> wrote in message news:c86e5ca8.0211161258.70aef47e_at_posting.google.com...
> Hello all,
> First off, the disclaimer to explain my ignorance of the Oracle
> Server. I am not an Oracle DBA. I am not an Oracle Developer. I am
> just the poor bastard in the office that knows enough about database
> design to be asked the database questions. Since I plead complete and
> utter ignorance to the nuts and bolts of Oracle, I ask this group for
> its help.
> A vendor we use has provided us with a schema of an Oracle database
> that their application utilizes. Aside from some basic normalization
> issues in the database design, I have a question about Oracle's
> efficiency in regards to using VARCHAR(256) as primary and foreign
> keys. The tables in question are without any type of surrogate keys.
> Would someone please explain (in relative layman's terms, I am a
> DBA, but not an Oracle DBA) the process Oracle goes through to
> optimize and utilize these values. The vendor swears up and down that
> this new design will increase performance. I am skeptical about
> that(Go figure, a DBA being skeptical about vendor developers'
> assertions). Do the inner workings of Oracle advocate the VARCHAR(PK)
> design and in what type of environment has to be in place for this
> scenario to work as advertised in terms of fragmentation, memory
> residence and allocation, process priority, etc. I will appreciate
> all of your responses and thank you in advance for your input.
Received on Sat Nov 16 2002 - 15:16:44 CST