I suspect the vendor was careless and used Oracle Designer to define the
tables. Designer uses varchar2(256) as a default when you create a field
(column) in a table. Like Sybrand said, numbers (I use a length of 11 as a
default) are more suited for primary and foreign keys. And primary keys
should only be populated by the database and not by the user or application.
"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
Received on Sun Nov 17 2002 - 09:24:11 CST
