Forum Stats

  • 3,782,041 Users
  • 2,254,585 Discussions
  • 7,879,899 Comments

Discussions

the number of leaf_blocks does not decrease after delete

710314
710314 Member Posts: 9
edited Aug 15, 2009 6:17AM in General Database Discussions
hi All,

this the example from Tom's book <<expert oracle database architecture>>, may anyone explain why the leaf_block oes not decrease to 523 after delete.
db version is 10.2.0.3, SEGMENT SPACE MANAGEMENT MANUAL in the tablespace system
[email protected]> drop table big_table purge;

Table dropped.

[email protected]> @big_table

Table created.


Table altered.

Enter value for 1: 500000
old   3:     l_rows number := &1;
new   3:     l_rows number := 500000;
Enter value for 1: 500000
old   9:         where rownum <= &1;
new   9:         where rownum <= 500000;

PL/SQL procedure successfully completed.


Table altered.


PL/SQL procedure successfully completed.


  COUNT(*)
----------
    500000

[email protected]> declare
  2  l_freelist_blocks number;
  3  begin
  4  dbms_space.free_blocks
  5  (
  6  segment_owner => user,
  7  segment_name => 'BIG_TABLE_PK',
  8  segment_type => 'INDEX',
  9  freelist_group_id => 0,
 10  free_blks => l_freelist_blocks
 11  );
 12  dbms_output.put_line(' block on freelist = ' || l_freelist_blocks);
 13  end;
 14  /
block on freelist = 0

PL/SQL procedure successfully completed.

[email protected]> select leaf_blocks from user_indexes where index_name = 'BIG_TABLE_PK';

LEAF_BLOCKS
-----------
       1043

[email protected]> delete  from big_table where id <= 250000;

250000 rows deleted.

[email protected]> commit;

Commit complete.

[email protected]> declare
  2  l_freelist_blocks number;
  3  begin
  4  dbms_space.free_blocks
  5  (
  6  segment_owner => user,
  7  segment_name => 'BIG_TABLE_PK',
  8  segment_type => 'INDEX',
  9  freelist_group_id => 0,
 10  free_blks => l_freelist_blocks
 11  );
 12  dbms_output.put_line(' block on freelist = ' || l_freelist_blocks);
 13  end;
 14  /
block on freelist = 520

PL/SQL procedure successfully completed.

[email protected]>
[email protected]> select leaf_blocks from user_indexes where index_name = 'BIG_TABLE_PK';

LEAF_BLOCKS
-----------
       1043
Edited by: Sidney Chen on Aug 15, 2009 3:10 PM
Tagged:

Best Answer

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,849 Gold Crown
    Accepted Answer
    Sidney Chen wrote:
    hi All,

    this the example from Tom's book <<expert oracle database architecture>>, may anyone explain why the leaf_block oes not decrease to 523 after delete.
    It's just the way it works - when a leaf block is emptied it is linked into the freelist, but also stays linked into the correct place in the index structure. It will be detached from the structure by a coalesce, or when Oracle needs to use a free block to handle new data.

    If you want a couple of reasonable guesses why:
    <ul>
    a) it probably makes read-consistency of indexes easier to deal with
    b) unlinking a block from the structure is expensive (you have to modify the blocks to the left, the right, and above) so Oracle takes the gamble that you're likely to re-insert data in the same block in the near future.
    </ul>

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk


    "Science is more than a body of knowledge; it is a way of thinking"
    Carl Sagan

Answers

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,849 Gold Crown
    Accepted Answer
    Sidney Chen wrote:
    hi All,

    this the example from Tom's book <<expert oracle database architecture>>, may anyone explain why the leaf_block oes not decrease to 523 after delete.
    It's just the way it works - when a leaf block is emptied it is linked into the freelist, but also stays linked into the correct place in the index structure. It will be detached from the structure by a coalesce, or when Oracle needs to use a free block to handle new data.

    If you want a couple of reasonable guesses why:
    <ul>
    a) it probably makes read-consistency of indexes easier to deal with
    b) unlinking a block from the structure is expensive (you have to modify the blocks to the left, the right, and above) so Oracle takes the gamble that you're likely to re-insert data in the same block in the near future.
    </ul>

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk


    "Science is more than a body of knowledge; it is a way of thinking"
    Carl Sagan
  • 710314
    710314 Member Posts: 9
    thanks Lewis for your expert advice.
  • 599445
    599445 Member Posts: 11
    you should rebuild the index , then it will change the index arch, so leaf block will release


    senior dba. http://www.if1000.com , welcome to visit.
This discussion has been closed.