Re: consistent gets

Mentre cercavo di capire la differenza tra una select e un drop database, Connor McDonald ha scritto:

>>
>> > The empty blocks will eventually be re-used or
>> > taken out of the index structure, but for very
>> > large deletes, the time-delay for a proper cleanout
>> > can be significant.
>>
>> [CUT]
>>
>> Thank you very much Jonathan a very clear explanation.
>> But this means that the index *is not* always balanced,
>> because in such situations the leaf blocks at the left hand
>> are empty.
>> The dml I've used was "delete from TABLE where cdate < data parallel ...."
>> so I think that *all* the blocks starting from the left side up to
>> a block in the middle (which contains data > data_used_on_dml)
>> should be deleted. If this is right....than I don't understand how can
>> the index stay balanced in such kind of situations.
>>
>> regards

> 
> Its 'balanced' in the sense that every leaf block is the same 'distance'
> from the root of the index.  But the distribution of index entries
> across leaf blocks may not be
> 
> hth
> connor
		

Ok Connor, I agree with you, but.....please make me understand. Suppose that our index has been created just now. It should be like this:

		     root 50
		     O
		    / \
		   O   O
                  /|   |\
		 O O   O O
 		/| |\ /| |\
      lowest 1 O O OO OO O O  higher 100
 	

The index is balanced, the key values are ordered. This means that the lowest value is in the leaf block at the left side, and the highest is in the opposite side.

Now I delete *all* rows where value < 30. The index can became something like this:

		     root 50
 		     O
		    / \
		   O   O
                   |   |\
		   O   O O
 		      /| |\
	lowest 30     OO O O  higher 100

But it should automatically re-build itself while the statement is going on to arrive at something like this:

		     root 65
 		     O
		    / \
		   O   O
                  /|   |\
		 O O   O O
 		 | |   | |
      lowest 30  O O   O O  higher 100

well, if the above is right the index is balanced and I should not use old blocks.

questions are:
Why I have to rebuild the deleted rows (and blocks) using consistent gets if there are no rows into such blocks??

After the rebuild index the consistent gets are disappeared (see my first post).
Why the index does not balance itself automatically while the delete statement is going on?

Where am I wrong?

thank's for your patient

fiuuuu, I don't like ASCII art :) Received on Tue Mar 22 2005 - 08:26:05 CST