Isn't the Windows GUID a fixed length value? Wouldn't CHAR(256) be a better
choice then?
"Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message
news:0ByB9.20571$%m4.7097_at_rwcrnsc52.ops.asp.att.net...
> 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 Mon Nov 18 2002 - 12:53:29 CST
