Re: indexes
- From: David Fitzjarrell <oratune@xxxxxxxxx>
- To: Joel.Patterson@xxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Mon, 17 Jan 2011 13:00:19 -0800 (PST)
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).
Understood.
“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).
Not true as Oracle implements a balanced B-tree index structure where all leaf
nodes are the same depth and leaf blocks are, on average, 75% full.
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.
Again, not true for reasons noted above.
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.
Still not true.
Coalescing does not help the DML. (even if it would help the size).
The index will reach a state of equilibrium and will, most likely, return to
the
size it was prior to the rebuild. Thus a rebuild won't help matters much, if
at
all, and with a balanced b-tree structure coalescing won't, either. Is this a
real performance problem you're seeing and attempting to rationalize based on
your concept of Oracle's B-tree index structure or is this an academic
exercise? Oracle's documentation provides a good description of how B-tree
indexes are implemented:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/schema.htm#sthref966
David Fitzjarrell
________________________________
From: "Joel.Patterson@xxxxxxxxxxx" <Joel.Patterson@xxxxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Cc: tim@xxxxxxxxx
Sent: Mon, January 17, 2011 2:39:05 PM
Subject: indexes
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:
