Re: Index query - monotonically increasing columns

"Norman Dunbar" <Oracle_at_MSSQLBountifulSolutions.co.uk> wrote in message news:baf81u$47k$1$8300dec7_at_news.demon.co.uk...
> Morning all,
>
> (I'm still bolloxed for the NGs at work, so here I am, early morning, at
> home !)
>
> I'm working on a little something at the moment in my spare time and I
have
> a table which will get bigger and bigger on a daily basis. Not too much
> expansion, but rows will be added daily - say about 50 - 100 rows per day.
>
> Rows will not be deleted normally, but they will be archived annually, so
> I'm planning to partition by year to allow easy archiving when it needs
> doing.
>
> The primary key for this table is a simple numeric column which will be
> generated via a sequence - I'm not worried about gaps in the call numbers,
> this is not a problem - but I am worried about my PK index for said
column.
> There is no other suitable value or column which can be used to uniquely
> identify a single row so it has to be the sequence I'm afraid.
>
> I hope that around 99.9% of queries on this table will be done using this
> number - simply because there is no other way in ! There are no batch
> processes to worry about.
>
> I know that in violation of a certain Oracle Myth that deleted rows will
> have their index slots reused as and when, but that an index based on a
> sequence is always increasing and will never re-use the slots which have
> been deleted (archived in fact) so index rebuilds are a possibility.
>
> Reverse indexes will spread the data, but stop me on index range scans -
or
> so I have read in an article by Tom Kyte. (I can't remember the full
> details, but these indexes were originally designed for OPS I believe.)

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. That doesn't in and of itself prevent you from doing range queries... merely, that since the range can be anywhere across the base of the index, you will either end up doing complete index scans, or the optimiser will decide not to bother, and do a tablescan instead. It's not, though, as if the range query returns some 'can't do that' error.

You only need to reverse where leaf-node contention is a major drama. That is most likely to happen in a RAC / OPS environment, but it could happen in a DML-intensive multi-cpu single-instance environment.

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.

>
> My thoughts so far are to prefix the sequence number with a randomly
> generated upper case letter or two to get a pseudo random spread of values
> (the PK becomes a CHAR(7) or CHAR(8) so I have a 1 or 2 letters and 6 zero
> padded digits rather than just a NUMBER).

Yikes! That's exactly what people did in Oracle 7, before reverse keys were invented. 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.

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.

> I'm going to test this out at work at lunchtime - if I can - and see what
it
> does to the index, but I was wondering if this is a halfway decent
> solution. As far as I can see, the data will be spread out nicely, as the
> letters are randomly generated, I'll be re-using index entries after rows
> have been archived and I shouldn't be troubled by ever increasing index
> depths as I would using the sequence alone.
>
> What do you think? Is it a good idea or am I barking again ?

Woof!

No... seriously, you don't have a problem with the number of inserts you're looking at, and the random-letters approach is an old-hat way of dealing with it in any case.

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

Apologies for not putting anything in Chapter 8... space constraints.

Regards
HJR
>
> Thanks & regards,
> Norman.
>
> PS. Beginning Oracle Programming Chapter 8, Indexes doesn't mention
> anything, neither does Jonathan or Tom in their (other) tomes.
>
> --
> Remove a certain other database to reply :o)
Received on Wed May 21 2003 - 06:10:55 CDT