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

"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message news:3dd9511f$0$9451$cc9e4d1f_at_news.dial.pipex.com...
> "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
> news:2JaC9.79258$g9.223489_at_newsfeeds.bigpond.com...
> > 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.
>
> This is a fair point and certainly I wouldn't want one to assume from my
> previous post that I'd always insist on surrogate keys. I'm dogmatic about
> very little (other than that the title Technical Architect is meaningless
> drivel). Database Management is surely a pragmatic discipline as well as a
> theoretical one. I do have a *preference* for surrogate keys though as
well
> as a preference to hide them from users.
>
> Of course if I had a 30 sq m rose bed to deal with it would take me all
year
> to look after it and the point would be moot.
>

Ah... you need a trip round Hyde Park, St.James' Park or Regents Park. Then you'll know what mulching is!!

> > I suppose I could have slapped a unique constraint on the site, feature
> and
> > plot number ... but that's now two indexes, not one.
>
> *If* its needed its needed.

Er, if I slap a unique constraint on columns, I get an index whether I like it or not. So I have the index caused by creating a surrogate primary key AND the index I would have got if I'd used the natural primary key, but it's now the index enforcing the unique constraint I've had to use because I'm trying to avoid natural keys.... I think!!

> > 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).
>
> No do go on (or at least point me at TFM) about the issues with reverse
key
> indexes.

Well, Jonathan's book has perhaps one of the better summaries of the issues. Lack of range scan ability. Block splits like crazy. And I once had the misfortune to see a reverse key index on a sequence that started at zero and went up in steps of 100. So the numbers (when reversed) were 001, 002, 003, 004.... ie, contention was merely transferred from the right-most leaf block to the left-most one! Something like that anyway -I can't remember the specifics now.

Regards
HJR
>
> --
> 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 - 15:24:27 CST