Re: indexes
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: Joel.Patterson@xxxxxxxxxxx
- Date: Wed, 19 Jan 2011 07:46:44 +0000
Joel
When all the entries have been deleted the index block will be put back on
the freelist and will become eligible for reuse. What is *much* more common
with this sort of data is that in fact not all the old entries are deleted
and so you end up with some, perhaps a lot, of sparsely populated leaf
blocks. A rebuild or more likely coalesce can help in this situation.
Reverse key indexes (listed in another reply) were invented for a specific
reason, namely to prevent pinging of the hot blocks at the current end of
the index in OPS (same logic applies to cache fusion in RAC) if this doesn't
apply to you then think carefully about the queries that this index
supports, key lookup should work fine, but range scans (typical with dates)
become much less efficient (they used not to be possible, but I *believe*
that to be no longer true. )
Incidentally when you say the index always grows have you done a sanity
check on the rate of insertion vs deletion - i.e is the table static in size
(measured by num_rows)
On 17 Jan 2011 20:40, <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
Other related posts:
