Re: indexes

From: Subodh Deshpande <deshpande.subodh_at_gmail.com>
Date: Wed, 19 Jan 2011 10:39:43 +0530
Message-ID: <AANLkTin_Ckf40bmoqwhrUK8TChVgW4R4ZpQs2evxO=UN_at_mail.gmail.com>

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_at_crowley.com> 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
==============================

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jan 18 2011 - 23:09:43 CST