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

Hi Norman,

Two things to consider:

1. If you create a local index on your partitioned table, then the index simply looks after itself when it comes time to archive your older data with the associated local index being dropped/exchanged/whatever as well.
2. Note that index pages get reused (i.e..don't waste space within the index structure) if *all* index entries within the page are deleted. Therefore if you were to delete a range of data, this may not affect the efficiency of your index as it simply just drops of the "older" nodes in the index.

Either way, what you're planning and the fear of index problems is probably a non-issue.

Cheers

Richard Received on Wed May 21 2003 - 03:29:12 CDT