Index query - monotonically increasing columns

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

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

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 ?

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)