The size of each index entry is the length of the indexed values plus a rowid and some overheads.
If you just delete the rows, then (as with tables) the blocks are still allocated to the table. Here I'll delete 10% of data. The allocated space remains the same however:
create tablespace uniform_ts
datafile size 1M autoextend on
segment space management manual
extent management local uniform size 32768;
create table t as
select rownum x, lpad('x', 700, 'x') y from dual connect by level <= 1000;
create index i on t (x, y) tablespace uniform_ts;
select blocks, bytes/1024 from user_segments
where segment_name = 'I';
BLOCKS BYTES/1024
---------- ----------
104 832
delete t
where x <= 100;
commit;
select blocks, bytes/1024 from user_segments
where segment_name = 'I';
BLOCKS BYTES/1024
---------- ----------
104 832So this doesn't make any extra space available to other objects. To reclaim this, you must shrink/rebuild the index:
alter index i rebuild;
select blocks, bytes/1024 from user_segments
where segment_name = 'I';
BLOCKS BYTES/1024
---------- ----------
92 736So how much space does removing rows free up within the index?
It depends!
Indexes are ordered data structures. So if you delete all the "first" entries this will empty blocks on the left hand edge of the index. So repeating the example above, deleting the first 100 rows (10% of them) empties the first 10 blocks. So this frees up for reuse (by the index):
drop table t purge;
create table t as
select rownum x, lpad('x', 700, 'x') y from dual connect by level <= 1000;
create index i on t (x, y) tablespace uniform_ts;
exec dbms_stats.gather_index_stats(user, 'i');
select leaf_blocks from user_indexes
where index_name = 'I';
LEAF_BLOCKS
---------------------------------------
100
delete t
where x <= 100;
commit;
exec dbms_stats.gather_index_stats(user, 'i');
select leaf_blocks from user_indexes
where index_name = 'I';
LEAF_BLOCKS
---------------------------------------
90So if you insert more values it can reuse them. If we add another 100 rows, starting with x = 1000 Oracle uses the ten blocks you just freed up:
insert into t
select rownum+1000 x, lpad('x', 700, 'x') y from dual connect by level <= 100;
commit;
exec dbms_stats.gather_index_stats(user, 'i');
select leaf_blocks from user_indexes
where index_name = 'I';
LEAF_BLOCKS
---------------------------------------
99But if you delete 100 rows spread throughout the index you only remove one row/block. Oracle can only reuse these if you insert the same (or similar) values back in. So if you insert rows starting with x = 1000, these have to go at the right hand edge of the index. The total size of the index increases:
drop table t purge;
create table t as
select rownum x, lpad('x', 700, 'x') y from dual connect by level <= 1000;
create index i on t (x, y) tablespace uniform_ts;
exec dbms_stats.gather_index_stats(user, 'i');
delete t
where mod(x, 10) = 0;
commit;
exec dbms_stats.gather_index_stats(user, 'i');
select leaf_blocks from user_indexes
where index_name = 'I';
LEAF_BLOCKS
---------------------------------------
100
insert into t
select rownum+1000 x, lpad('x', 700, 'x') y from dual connect by level <= 100;
commit;
exec dbms_stats.gather_index_stats(user, 'i');
select leaf_blocks from user_indexes
where index_name = 'I';
LEAF_BLOCKS
---------------------------------------
109If you use a sequence to assign x, the free space is effectively lost. To reclaim this you need to shrink/rebuild the index. For more discussion about this read:
https://richardfoote.wordpress.com/2008/02/08/index-rebuild-vs-coalesce-vs-shrink-space-pigs-3-different-ones/ Also note that if you delete a significant fraction of the rows, a rebuild may reduce the number of branch blocks. So for indexes with a couple of branch levels a big delete could free up these too.
Note: deleting rows can cause an index's size to increase:
https://richardfoote.wordpress.com/2015/06/29/quiz-time-why-do-deletes-cause-an-index-to-grow-solution/