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

No, that's a perfectly reasonable (and standard) way of doing it in pre-Oracle 8 days (ie, before Reverse Keys were invented).

The one I've heard of is... get your unique sequence number, then grab a letter at random and pre-pend it. So 100 becomes F100, 200 becomes X200, 300 becomes H300 and so on. When you insert into the index, your F's and H's go one end, the X's and Z's go the other... monotonically incrementing sequence number with no contention.

Only trouble with that approach (and the one you describe) is that requires a developer to write some code.

'Nuff said!! (Apologies to all my developer friends out there).

Reverse Keys were a way of achieving the same thing without the need for developers (hooray!).

Regards
HJR "Jim" <no-spam_at_no-spam.org> wrote in message news:aioitu8q6apargdtpr5g7ll2c0744lhkc6_at_4ax.com...
> In the system we built that used a sequential 9-digit ID number as
> the primary key we tried to ease contention issures by actually
> incrementing only the low-order 8 numbers and then using a MOD(10)
> algorithm to generate a unique first digit prefix. This way the
> generated first digit functioned as a check digit for the entire
> 9-digit number, and we scattered the new records across 10 hot spots
> instead of just one. Of course, that was many years ago and this may
> not be as helpful these days.
>
> On Tue, 19 Nov 2002 06:05:00 +1100, "Howard J. Rogers"
> <howardjr2000_at_yahoo.com.au> wrote:
>
> >I have two problems with surrogate keys.
> >
> >First, they ensure uniqueness of an entry which oughtn't to be permitted.
As
> >an example, a landscaping application I once had a hand in permitted
users
> >to say 'on site X, there is a Rose bed (BR1), plot 1, and it's 30 sq.m'.
> >There is also a BR1 plot 2 of 25 sq.m. It was important not to add
another
> >BR1 plot 1 or plot 2, since you'd end up pruning the wrong rose bed. But
if
> >all landscaping features were being added with surrogate primary keys,
there
> >would have been nothing to stop the insertion of such a duplicate record,
> >since the surrogate key would have been unique.
> >
> >I suppose I could have slapped a unique constraint on the site, feature
and
> >plot number ... but that's now two indexes, not one.
> >
> >Second, indexes on surrogate keys (ie, monotonically incrementing
sequence
> >numbers) are a royal pain in the contention stakes, and I might not have
> >wanted to pay for the partitioning option so that I could have partition
the
> >table to avoid the contention (and the less said about reverse keys the
> >better).
> >
> >Just my square metre's worth.
> >Regards
> >HJR
> >
> >
> >"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message
> >news:3dd91bd1$0$8514$cc9e4d1f_at_news.dial.pipex.com...
> >> "Jim" <no-spam_at_no-spam.org> wrote in message
> >> news:lh1itu0ibhtnjt28jal4ne6ou7e6j1n267_at_4ax.com...
> >> > I confess I don't understand the preference for the use of a
surrogate
> >> > key instead of a natural key except in special cases.
> >> >
> >> > I can see that a surrogate, "artificial" key would have some value IF
> >> > all "natural" columns in a table must be updatable, IF no 'natural'
> >> > column, or reasonable combination of 'natural' columns, would be
> >> > unique, and IF all updating of the table would be perfomed by
> >> > application programs able to deal with the essentially meaningless
> >> > surrogate key.
> >> >
> >> > On the other hand, use of a 'natural' key composed of column(s)
> >> > understood and meaningful to the user would, it seems to me, yield a
> >> > more straightforward, easier for the user to understand design.
> >> >
> >> > Am I missing something (probably)?
> >>
> >> Natural keys tend, in practice, to be either
> >>
> >> 1. Updateable.
> >> 2. Non-unique when business rules change or in the event of design
error.
> >>
> >> In addition I'm tempted to ask why should the user know about a key at
> >all.
> >> Most answers to this question assume a knowledgable user familiar with
> >> database concepts (for example a report developer) who is quite capable
of
> >> using surrogate keys.
> >>
> >>
> >> --
> >> Niall Litchfield
> >> Oracle DBA
> >> Audit Commission UK
> >> *****************************************
> >> Please include version and platform
> >> and SQL where applicable
> >> It makes life easier and increases the
> >> likelihood of a good answer
> >> ******************************************
> >>
> >>
> >
>
Received on Mon Nov 18 2002 - 16:19:58 CST