Re: Index query - monotonically increasing columns

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<TZIya.39166$1s1.555553_at_newsfeeds.bigpond.com>...

> Not quite... by reversing the byte order before indexing, you cause the
> index entries to be inserted across the entire base of the index, and thus
> avoid contention for the last (right-most) leaf node.
Spookily enough, I'm just reading up on this at the moment. In Chapter 8 of all places :o)

> If you're anticipating 100 inserts a day, this isn't going to be an issue
> for you. 100 per second, and it would be.
So let's assume that the app is very heavily used and leads to about 100 inserts a second, where do we go from here then ? What would be the 'ideal' solution to the sequence problem ?

> Yikes! That's exactly what people did in Oracle 7, before reverse keys were
> invented.

Oops - I never did any Oracle 7 development, I've just re-invented the wheel !

> Problems with it (apart from it not being needed in your case) are
> that it relies on developers to write code (always a dodgy enterprise!!),
> and there's code maintenance to worry about, and what happens if the
> procedure is ever invalidated... etc etc etc.
I'm the sole developer - so the developer skills are not an issue (Famous last words!). On the other hand, I intend a trigger to generate the PK and hence the indexed value - the user has no input - har har har !

> It's an old-fashioned approach, and achieves nothing that reversing wouldn't
> do anyway: it still means that an ascending sequence number ends up being
> inserted across the entire base of the index, and range queries are just as
> problematic as they would have been with reversing.
Actually, thinking about it more, range queries are just not going to happen, so it is even less of a problem in this app than at first thought. As you say in chapter 8, '... it is not as disasterous as it sounds, since it is highly unlikely that you would ever *want* to select a range of employees by their employee number ...' - I cannot help but agree !

In this app, a help desk sort of thing, call numbers are individual entities which exist in isolation from each other - unless they get linked together as being part of the same problem. I think I'm just worrying unduly in this case.

> Woof!

Ah well, 'woof' is better than 'yap yap' - I hate small dogs! I'm a big dog fan. Dingos are nice too - I've met a few in my time. They don't bark and they don't smell in the wet - how civil is that?

<SNIP a bit>

> Incidentally, reversing is not necessariily the best way of dealing with it,
> either. A nice hash partition on the index might be a better approach, since
> that will also ensure that an ascending number ends up being inserted into a
> variety of different segments (ie, partitions).
Hash partitions - they are Oracle 9i though are they not? This app will be 8i and 9i based (I know about 8i, but some things are not under my control !)

> Apologies for not putting anything in Chapter 8... space constraints.
And please accept mine for not reading/remembering it better. You do discuss 'monotonously :o) increasing sequence numbers' in chapter 8 under the Reverse Key Indexes section.

Cheers,
Norman - behind Google :o( Received on Thu May 22 2003 - 03:22:05 CDT