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

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 Sat Nov 16 2002 - 15:11:13 CST