By Franck Pachot
.
Yesterday while giving our Oracle tuning workshop I discussed with the customer about a job they have that rebuilds indexes every Sunday. Except in very rare cases Oracle indexes are maintained so that free space is reused by further inserts. But an index is an ordered structure. When we insert from a sequence, the value is always increasing, and go at the end of the index. And when we delete old data we delete index entries at the beginning of the index.
Is this a case where we need to manage it ourselves?
Test case
As usual I reproduce the issue. Here is my DEMO table with a DEMOPK index on the primary key:
SQL> create table DEMO (id number constraint DEMOPK primary key);
Table created.
I insert 10000 rows:
SQL> begin
2 for i in 1..1e4 loop
3 insert into DEMO values(i);
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
gather and check the stats:
SQL> exec dbms_stats.gather_table_stats(user,'DEMO');
PL/SQL procedure successfully completed.
SQL> select blocks,blevel,leaf_blocks from user_indexes join user_tables using(table_name) where index_name='DEMOPK';
BLOCKS BLEVEL LEAF_BLOCKS
---------- ---------- -----------
20 1 18
So I have 1 branch and 18 leaf blocks.
Fragmentation
I’ll check fragmentation from a user point of view. Having too much free space in leaf blocks is a problem with index range scan only. So let’s fo an index range scan from the beginning to the end of the index:
SQL> alter session set statistics_level=all;
Session altered.
SQL> select /*+ index(DEMO) */ count(*) from DEMO;
COUNT(*)
----------
10000
SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 7b6qc9m1cw3zd, child number 0
-------------------------------------
select /*+ index(DEMO) */ count(*) from DEMO
Plan hash value: 3019291478
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 19 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 19 |
| 2 | INDEX FULL SCAN| DEMOPK | 1 | 10000 | 10000 |00:00:00.01 | 19 |
-------------------------------------------------------------------------------------
Exactly what we expected: 19 blocks reads is 1 branch and 18 leaves.
I have a script that does the same – range scan on an index – and shows how many index entries we have in each block. The script is here: How to measure Oracle index fragmentation. Let’s run it on my index, with a bucket size large enough to see all blocks:
SQL> @ index_fragmentation.sql
ID -> ID rows/block bytes/block %free space blocks free
---------- -- ---------- ---------- ----------- ----------- ---------- -----
1 -> 1 578 8566 1
579 -> 579 571 8559 1
1150 -> 1150 571 8559 1
1721 -> 1721 571 8560 1
2292 -> 2292 571 8559 1
2863 -> 2863 571 8559 1
3434 -> 3434 571 8559 1
4005 -> 4005 571 8560 1
4576 -> 4576 571 8559 1
5147 -> 5147 571 8559 1
5718 -> 5718 571 8560 1
6289 -> 6289 571 8559 1
6860 -> 6860 571 8559 1
7431 -> 7431 571 8559 1
8002 -> 8002 571 8560 1
8573 -> 8573 571 8559 1
9144 -> 9144 571 8559 1
9715 -> 9715 286 4287 47 1 oo
Here are our 18 leaf blocks, covering values from 1 to 10000 (the ID displayed is the first one in each bucket – blocks here). The blocks are full (size is an approximation so this is why it’s a bit higher than 8k), with about 570 entries per block. This is expected because when we insert increasing values, the block split fills the block instead of doing a 50-50 split.
delete insert lifecycle
Here is what I want to reproduce: delete old rows at the beginning of the index and insert new rows at the end. I’ll do that for the same number of rows:10000 so I’m sure I’ve delete rows from all those 18 leaf blocks.
SQL> begin
2 for i in 1..1e4 loop
3 delete from DEMO where id=i;
4 commit;
5 insert into DEMO values(i+1e4);
6 commit;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
Then run my index range scan:
SQL> select /*+ index(DEMO) */ count(*) from DEMO;
COUNT(*)
----------
10000
SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 7b6qc9m1cw3zd, child number 0
-------------------------------------
select /*+ index(DEMO) */ count(*) from DEMO
Plan hash value: 3019291478
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 24 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 24 |
| 2 | INDEX FULL SCAN| DEMOPK | 1 | 10000 | 10000 |00:00:00.01 | 24 |
-------------------------------------------------------------------------------------
Did I double the number of blocks to read? No.
Do you think that we lost some space because we read 24 blocks instead of 19? Look at the numbers. The new numbers are above 10000 and are larger than the initial ones. It’s 4 bytes vs. 3 bytes.
Don’t believe me?
SQL> select min(rownum),max(rownum),sum(vsize(rownum)) from (select * from dual connect by 1000>=level),(select * from dual connect by 20>=level) group by ceil(rownum/10000);
MIN(ROWNUM) MAX(ROWNUM) SUM(VSIZE(ROWNUM))
----------- ----------- ------------------
1 10000 29801
10001 20000 39899
Yes… No place for guesses and myth… Everything can be measured… Do you know how many block we need when data in 18 blocks are increased by that ratio? 18 * (4/3) = 24 so we are not bad at all.
The fact is that the 18 leaf blocks has only been increased to 20 leaf blocks:
SQL> @ index_fragmentation.sql
ID -> ID rows/block bytes/block %free space blocks free
---------- -- ---------- ---------- ----------- ----------- ---------- -----
10001 -> 10001 266 4254 47 1 oo
10267 -> 10267 533 8523 1
10800 -> 10800 533 8522 1
11333 -> 11333 533 8523 1
11866 -> 11866 533 8523 1
12399 -> 12399 533 8522 1
12932 -> 12932 533 8523 1
13465 -> 13465 533 8523 1
13998 -> 13998 533 8522 1
14531 -> 14531 533 8523 1
15064 -> 15064 533 8523 1
15597 -> 15597 533 8522 1
16130 -> 16130 533 8523 1
16663 -> 16663 533 8523 1
17196 -> 17196 533 8522 1
17729 -> 17729 533 8523 1
18262 -> 18262 533 8523 1
18795 -> 18795 533 8522 1
19328 -> 19328 533 8523 1
19861 -> 19861 140 2237 72 1 ooo
and they are all full – except first and last one.
This is optimal. Do the same test case and you will see that if you coalesce or shrink the index then the number of blocks will not change. More info about it in a previous blog post: index coalesce vs. shrink vs rebuild
Is it new?
Ok, I’ve run my tests on 12c and you want to know if it’s something new. No it’s not new.
Oracle 7.3.3 reuses the deleted space as well:
It’s the same test case except that here I’m with 2k block size.
Index rebuild
Do you think index rebuild can help, or at least is not harmful?
SQL> alter index DEMOPK rebuild;
Index altered.
SQL> @ index_fragmentation.sql
ID -> ID rows/block bytes/block %free space blocks free
---------- -- ---------- ---------- ----------- ----------- ---------- -----
10001 -> 10001 478 7644 5 1
10479 -> 10479 479 7659 5 1
10958 -> 10958 479 7659 5 1
11437 -> 11437 479 7659 5 1
11916 -> 11916 478 7644 5 1
12394 -> 12394 479 7659 5 1
12873 -> 12873 479 7659 5 1
13352 -> 13352 479 7659 5 1
13831 -> 13831 479 7659 5 1
14310 -> 14310 478 7644 5 1
14788 -> 14788 479 7659 5 1
15267 -> 15267 479 7659 5 1
15746 -> 15746 479 7659 5 1
16225 -> 16225 479 7659 5 1
16704 -> 16704 478 7644 5 1
17182 -> 17182 479 7659 5 1
17661 -> 17661 479 7659 5 1
18140 -> 18140 479 7659 5 1
18619 -> 18619 478 7644 5 1
19097 -> 19097 479 7659 5 1
19576 -> 19576 425 6794 15 1
The index rebuild has increased the size of the index. One more leaf block here. Because it has left 5% of free space in each block. And that free space will never be reused because there are no future rows that will go there.
Conclusion
Is the free space reused in an index on a sequence – always increasing – when we are purging old data?
Answer is: yes… unless to are doing regular index rebuilds.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/12/oracle-square.png)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)