1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80
|
SQL > begin
2 p_check_free_space (user, MY_IND, 'INDEX');
3 end;
4 /
Number of Blocks with 0-25% free space = 0 -------> Total Bytes = 0
Number of Blocks with 25-50% free space = 2964 -------> Total Bytes = 23.15625
Number of Blocks with 50-75% free space = 0 -------> Total Bytes = 0
Number of Blocks with 75-100% free space = 0 -------> Total Bytes = 0
Number of Full Blocks with no free space = 436 -------> Total Bytes = 3.40625
Total Bolcks ______________________________
3400
Total Size MB______________________________
27.2
PL/SQL procedure successfully completed.
SQL > alter index MY_IND coalesce;
Index altered.
SQL > begin
2 p_check_free_space (user, 'MY_IND', 'INDEX');
3 end;
4 /
Number of Blocks with 0-25% free space = 0 -------> Total Bytes = 0
Number of Blocks with 25-50% free space = 3224 -------> Total Bytes = 25.1875
Number of Blocks with 50-75% free space = 0 -------> Total Bytes = 0
Number of Blocks with 75-100% free space = 0 -------> Total Bytes = 0
Number of Full Blocks with no free space = 176 -------> Total Bytes = 1.375
Total Bolcks ______________________________
3400
Total Size MB______________________________
27.2
PL/SQL procedure successfully completed.
SQL > select
2 keys_per_leaf, count(*) blocks
3 from (
4 select sys_op_lbid (144311, 'L', ptc.rowid) block_id,
5 count (*) keys_per_leaf
6 from my_table ptc
7 where ptc.ind_col is not null
8 group by sys_op_lbid (144311, 'L', ptc.rowid)
9 )
10 group by keys_per_leaf
11 order by keys_per_leaf
12 ;
KEYS_PER_LEAF BLOCKS
------------- ----------
1 5
2 7
3 2
5 5
8 1
10 1
11 1
12 1
13 1
15 2
16 1
18 1
20 1
38 1
50 1
68 1
108 1
128 1
155 1
172 1
199 1
209 2
262 1
320 1
323 1
377 119
26 rows selected. |