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

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: