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

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:xLdC9.79319$g9.223332_at_newsfeeds.bigpond.com...
>
> "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>
>
> 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.

You don't get it because there's nothing to get. I didn't seek to suggest that reverse keys split more than a conventional index. Merely that in comparison with a non-reversed index *on the same data* it would split like crazy. An ordinary index on a true monotonically incrementing sequence number doesn't split at all, except at the (logically) right most edge..

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

Who said anything about block splits "per se"? You're comparing apples with apples. I was comparing apples and bananas.

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

As I said, I can't remember the specific details. But if you had a leaf block that took all the 'zero' entries, all 3 of your entries go into that same block.

The general point is that a bad choice of sequence can merely transfer the contention. You need a sequence that, when reversed, sprays back and forth across the base of the index.

HJR
> 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:29:37 CST