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

Russ Clancy wrote:
> 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.
It's difficult to know to which of the many threads I should reply, so I'll just stick my comments on the OP.

There are times when you ask yourself about the probability of some natural key changing due to business rules or other rules. For example, we have a table of state codes and state names, where the primary key is the state code (like CA = California). Before you complain, the table existed prior to my employment with this company and there's a *lot* of code already in existence that relies on it being this way. Undoubtedly (?), the original designers asked themselves: should we make up a surrogate PK? What are the chances of the state codes changing? Will CA always be the code for California? Well, of course it will. So there's probably no harm in using a natural key in this case. True, we had to add a column that identified the "state" as a true state or a territory. True, if we ever go global, we might run into trouble ... but then we'd have to rethink our simple-minded, USA-specific table anyhow.

The second point I'd like to make refers to the large (256-byte) key. When I started with my company, there was a widespread belief among the developers that Oracle stored any column smaller than VARCHAR2(2048) as a right space-filled column (like a CHAR). I have no clue where this idea came from, but maybe there's someone out there spreading rumors that anything less than VARCHAR2(256) will waste space as well. We have so many 2048-byte columns in our database because of this belief ... but I've since shrunk most of them after 'disproving' the developers's belief with a 'select dump(bigcol)...' command. Sometimes what developers believe can surprise you. Received on Mon Nov 18 2002 - 16:57:39 CST