Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Is the use of VARCHAR(256) as Primary Keys preferred in Oracle?

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

From: Paul Brewer <paul_at_paul.brewers.org.uk>
Date: Sun, 17 Nov 2002 20:48:59 -0000
Message-ID: <3dd8142b_1@mk-nntp-1.news.uk.worldonline.com>


"Sybrand Bakker" <gooiditweg_at_sybrandb.demon.nl> wrote in message news:ctcdtuo5v14rg0dh7bdd8bhhvi7nomlgad_at_4ax.com...
> On 16 Nov 2002 12:58:25 -0800, rclancy_at_rotech.com (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.
>
>
> Varchar2 comparisons (which are inevitable when you use Varchar2 as PK
> and FK) are by design slower than number comparisons.
> If the vendor insists varchar2 will increase performance, I would
> recommend you to have him sign off a document, which will allow you to
> sue him when his assertion appears to be false, which is inevitably
> going to happen, at the demise of your company.
>
> Regards
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address

Sybrand,

No wish to offend, but I'm not sure I go along with you entirely on this. Personally, if a 'natural' VARCHAR2 key of reasonable length (say 4 to 12) were available, I for one would not be inclined to invent a numeric surrogate key.
As someone else suggested, the use of VARCHAR2 (or VARCHAR) of length 256 may be simply due due to insufficient analysis, or (same thing really), lazy acceptance of tool defaults.

Regards,
Paul Received on Sun Nov 17 2002 - 14:48:59 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US