Re: Index query - monotonically increasing columns

"Norman Dunbar" <norman.dunbar_at_lfs.co.uk> wrote in message news:3078e2fe.0305210538.1f728ffe_at_posting.google.com...
> Afternoon Richard, Niall,
>
> as ever, thanks for your comments. As you say, in this experiment, it
> makes not a jot of difference. My experimenting showed that with a
> random letter at the start, the index depth is quite happily 2 for
> ages and ages and ages !
>
> In a hypothetical situation though, with millions of rows being
> generated on a sequence, would this be a good way of avoiding index
> problems - and I'm talking theory/good practice here. Pure ascending
> numbers being indexed with rows being deleted is going to trash the
> index over time. Slapping a random letter in front is going to spread
> the data out - similar to indexeing on someones surname perhaps - and
> will allow index entries to be re-used over time.
>
> Or, is there another way .......? :o)

Maybe my other post hasn't shown up yet.... but what difference is there between random letters and reversing the byte order? None is the short answer. Both techniques cause the entire base of the index to receive new entries. But one relies on developer code. One on Oracle code.

And yes, there is another way. What you're after is a mechanism which, whatever the particular values being inserted, ensures the inserts take place evenely across a lot of physical locations (thus eliminating contention issues). That is a perfect description of hash partitionining.

Regards
HJR Received on Wed May 21 2003 - 14:38:40 CDT