Index size becomes larger after rebuild in Oracle

Let's look this scenario for Oracle: after 5% rows randomly deleted from a table, will index size become smaller if we rebuild index?

The answer is may not, and the size could be even larger if the index is based on sequence or identity columns.

Here are steps to reproduce it:

create table t (
    id int generated as identity,
    value varchar2(10),
    last_update date default sysdate,
    constraint t_pk primary key(id));

-- set sort_area_size to avoid "ORA-30009: Not enough memory for CONNECT BY operation"
alter session set workarea_size_policy=manual;
alter session set sort_area_size=1000000000;

insert into t (value) select rpad('a',10,'a') from dual connect by level <= 10000000;

commit;

-- randomly delete 5% records
begin
  for i in 1..10000000*0.05 loop
    delete from t where id>dbms_random.value(1,10000000) and rownum=1;
    if mod(i,1000) = 0 then
      commit;
    end if;
  end loop;
  commit;
end;
/

SQL> select bytes from user_segments where segment_name ='T_PK';

           BYTES

----------------

     167,772,160

SQL> alter index t_pk rebuild;

Index T_PK altered.

SQL> select bytes from user_segments where segment_name ='T_PK';

           BYTES

----------------

     176,160,768

The index size has been increased even 5% rows removed. 

Why? Originally when rows inserted into the index, it occupied 100% of the index leaf blocks. The default index rebuild will reserve 10% free space.

Before Rebuild:

SQL> analyze index t_pk validate structure;

Index T_PK analyzed.

SQL> select blocks,lf_rows,lf_blks,pct_used from index_stats where name='T_PK';

    BLOCKS    LF_ROWS    LF_BLKS   PCT_USED

---------- ---------- ---------- ----------

     20480    9842163      20000         99

After Rebuild:

SQL> analyze index t_pk validate structure;

Index T_PK analyzed.

SQL> select blocks,lf_rows,lf_blks,pct_used from index_stats where name='T_PK';

    BLOCKS    LF_ROWS    LF_BLKS   PCT_USED

---------- ---------- ---------- ----------

     21504    9500000      21159         90

Rebuild with PCTFREE=0

SQL> alter index t_pk rebuild pctfree 0;
Index T_PK altered.

SQL> select bytes from user_segments where segment_name ='T_PK';
           BYTES
----------------
     159,383,552

SQL> analyze index t_pk validate structure;
Index T_PK analyzed.

SQL> select blocks,lf_rows,lf_blks,pct_used from index_stats where name='T_PK';
    BLOCKS    LF_ROWS    LF_BLKS   PCT_USED
---------- ---------- ---------- ----------
     19456    9500000      19001        100