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

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:YLcC9.79299$g9.223540_at_newsfeeds.bigpond.com...
> "Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message
> news:3dd9511f$0$9451$cc9e4d1f_at_news.dial.pipex.com...

<snip>

> > > 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.

Hi Howard,

It's early for me (sun has only been up a few hours) so forgive me if I'm missing something really simple here (and I don't have Jonathon's book, please don't tell him ;)

The issues you've mentioned regarding reversed key indexes.

1. Lack of range scan ability: Agreed
2. Block splits like crazy: Why is this more of an issue with RKI than with a conventional, non monotonically incrementing index ?. Providing the distribution truly is even across the leaf pages and the pctfree is appropriate, I don't quite get it. Also, the biggest hit with a block split is the I/O required to get the new block. This hit is going to be much worse if such a monotonically incrementing index were not reversed as the "right most" block is continually being filled with a new block continually being requested. I can see how by not reversing the index you get to "fill" the bugger up more compactly, I just don't see the issue with block splits per se.
3. Going up in steps of 100. I don't see the issue with this one as well ? Why does the "left most" block now get all the contention ? All that is happening is we have 00 in front of all numbers, the remaining numbers get reversed and are distributed evenly as a result.

123400 => 004321
123500 => 005321
123600 => 006321

and so on ...

What am I missing ?

Cheers

Richard

>
> 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 - 17:38:08 CST