Re: indexes
- From: Subodh Deshpande <deshpande.subodh@xxxxxxxxx>
- To: Joel.Patterson@xxxxxxxxxxx
- Date: Wed, 19 Jan 2011 10:39:43 +0530
check whether reverse key index instead of traditional b-tree is useful, if
the values are in sequential, unique or as you said not necessary primary
key reversekey index will be more useful I suppose.
thanks..subodh
On 18 January 2011 02:09, <Joel.Patterson@xxxxxxxxxxx> wrote:
>
> I would like to verify this assumption.
>
> You have an index, not necessarily the primary key, (10g) and the columns
> position is 1, or with some indexes 2, and is a DATE data type.
> The data is inserted into the tables each day by “todays” DATE… yesterday,
> today, tomorrow, and is deleted from the table by the oldest, oldest,
> next_oldest etc.
> It is a HEAP index (using a binary search by default).
>
> “The index always grows”.
>
> Lets start with a fresh rebuild:
>
> Then data is inserted into the index always on one side (the most
> recent). Data is always deleted on the other side (the least recent).
>
> Even if all the records in each block where to be deleted, oracle would
> never reuse the leaf node because data is always being inserted on the ‘most
> recent’ side of the index.
>
> Eventually the ‘middle’ of the index, (the start of the binary search),
> becomes the least recent entry… thus the index is always traversed from that
> point to the most recent side.
> The index has been skewed in that everything is to the most recent side of
> the entry point.
>
> Coalescing does not help the DML. (even if it would help the size).
>
> Any comments on any of these points is appreciated,
>
> Thank you very much.
>
> Joel Patterson
> Database Administrator
> 904 727-2546
>
>
>
--
==============================
DO NOT FORGET TO SMILE TODAY
==============================
Other related posts:
